浏览一下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