RedHat 5.5 配置 MySQL AB复制

浏览一下5.5官档上关于replication的章节:MySQL.com/doc/refman/5.5/en/replication-howto.html

总结了一下关于AB复制的步骤:

(1)设置主服务器的my.cnf 开启二进制日志记录设置serverid

[mysqld]

log-bin=mysql-bin

server-id=1

innodb_flush_log_at_trx_commit=1

sync_binlog=1

(2)设置从服务器的serverid

[mysqld]

server-id=2

(3)设置复制帐户,并授权

create user 'gabriel'@'%.linuxic.com' identified by 'gabriel0814';

grant replication slave on *.* to 'gabriel'@'%.linuxic.com ';

(4)锁定数据库状态,读取主服务器二进制日志的状态

flush tables with read lock;

show master status;

(5)使用mysqldump创建数据库快照,释放锁

mysqldump --all-database --lock-all-tables > dbdump.db

unlock tables;

(6)在从服务器上准备复制:

stop slave;

mysql < fulldb.dump

change master to

master_host='applinux002.linuxic.com',

master_user='gabriel',

master_password='gabriel0814',

master_log_file='',

master_log_pos= ;

start slave;

以上步骤在自己的虚拟机上测试是通过的,在复制搭建完毕后,由于权限的问题,去掉通配符%改为实际的主机名,AB复制成功。

在生产线上具体实施步骤如下:

(1)    更改master数据库my.cnf参数:

[root@applinux002 data]# cat /etc/mysql/my.cnf | grep -v ^# | grep -v ^$

[client]

port          = 3306

socket        = /etc/mysql/mysqld.sock

[mysqld]

datadir = /md3200i/data1/mysql/data

port          = 8808

socket        = /etc/mysql/mysqld.sock

skip-external-locking

key_buffer_size = 384M

max_allowed_packet = 1M

table_open_cache = 512

sort_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size = 32M

thread_concurrency = 24

log-bin=mysql-bin

server-id      = 1

innodb_data_home_dir = /md3200i/data1/mysql/data

innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend

innodb_log_group_home_dir = /md3200i/data1/mysql/data

innodb_buffer_pool_size = 2048M

innodb_additional_mem_pool_size = 20M

innodb_log_file_size = 512M

innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 50

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 256M

sort_buffer_size = 256M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

(2)    更改slave数据库my.cnf参数:

[root@applinux003 mysql]# cat /etc/mysql/my.cnf | grep -v ^# | grep -v ^$

[client]

port          = 3306

socket        = /etc/mysql/mysqld.sock

[mysqld]

port          = 8808

socket        = /etc/mysql/mysqld.sock

skip-external-locking

key_buffer_size = 384M

max_allowed_packet = 1M

table_open_cache = 512

sort_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size = 32M

thread_concurrency = 8

server-id      = 2

innodb_data_home_dir = /md3200i/data1/mysql/data

innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend

innodb_log_group_home_dir = /md3200i/data1/mysql/data

innodb_buffer_pool_size = 2048M

innodb_additional_mem_pool_size = 20M

innodb_log_file_size = 512M

innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 50

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 256M

sort_buffer_size = 256M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:http://www.heiqu.com/34aed98a97fd7234475a7ed5c8dd9d41.html