Ubuntu MySQL主从同步配置

系统：Ubuntu 16.04 x64MySQL 5.5.16##一、配置Master服务器1.修改配置文件my.cnf (Ubuntu下在/etc/mysql/my.cnf)[mysqld]log-

系统：

• Ubuntu 16.04 x64
• MySQL 5.5.16

##一、配置Master服务器

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

[mysqld]log-bin=mysql-binserver-id=1binlog-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-binserver-id=2replicate-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;