Ubuntu MySQL主从同步配置

系统：

• Ubuntu 16.04 x64
• MySQL 5.5.16

##一、配置Master服务器

1.修改配置文件my.cnf (Ubuntu下在/etc/mysql/my.cnf)

[mysqld]
log-bin=mysql-bin
server-id=1
binlog-do-db=test1              # 要同步的数据库
binlog-do-db=test2
#binlog-ignore-db=mysql    # 忽略的数据库
#binlog-ignore-db=sys

配置好后重启mysql

sudo /etc/init.d/mysql restart

2.在主服务器上添加replication账号

mysql> grant replication slave on *.* to 'myreplication'@'%' identified by '123456';
mysql> flush privileges;

3.锁定数据库

mysql> flush tables with read lock;

4.查看当前主服务器master状态，并导出数据库

mysql> show master status;

mysqldump -uroot -p wks > /path/wks.sql

5.解锁数据库

unlock tables

##二、配置Slave服务器

1.修改配置文件my.cnf (Ubuntu下在/etc/mysql/my.cnf)

[mysqld]
log-bin=mysql-bin
server-id=2
replicate-do-db=test1    # 要同步的数据库
replicate-do-db=test2 
#replicate-ignore-db=mysql
#replicate-ignore-db=sys

配置好后重启mysql

sudo /etc/init.d/mysql restart

2.导入主服务器数据库

mysql -u root -p test < /path/test.sql
或
mysql> source /path/test.sql

3.停止slave，设置主服务器的参数

mysql> slave stop;
mysql> change master to
    -> master_host='192.0.0.1',
    -> master_user='myreplication',
    -> master_password='123456',
    -> master_port=3306,
    -> master_log_file='mysql-bin.000002',
    -> master_log_pos=79,
    -> master_connect_retry=60;
mysql> slave start;

4.查看主从服务器的状态

mysql> show processlist;
mysql> show slave status\G;

转载于:https://my.oschina.net/zhanlzy/blog/1036647