1.准备工作
服务器一,操作系统Debian 8,IP地址192.168.15.10,主机名master,mysql版本5.5。
服务器二,操作系统Debian 8,IP地址192.168.15.11,主机名slave,mysql版本5.5。
2.配置
a)配置Master
root@master:/# vim /etc/mysql/my.cnf ... server-id = 1 log_bin = /var/log/mysql/mysql-bin.log #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name ... :wq root@master:/# service mysql restart root@master:/# mysql -uroot -p mysql> GRANT REPLICATION SLAVE ON *.* to 'slave001'@'%' identified by '123456'; mysql> flush privileges; mysql> flush tabels with read lock; mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 108 | | | +------------------+----------+--------------+------------------+ mysql>
此时不要退出,等从库设置并启动完成后还需要执行unlock tables。
mysql> unlock tables;
b)配置slave
root@slave:/# vim /etc/mysql/my.cnf ... server-id = 2 log_bin = /var/log/mysql/mysql-bin.log #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name ... :wq root@slave:/# service mysql restart root@slave:/# mysqldump -h192.168.15.10 -uroot -p –-all-databases –-add-drop-database –-add-drop-table > dump.sql root@slave:/# mysql -uroot -p < dump.sql root@slave:/# mysql -uroot -p mysql> change master to master_host='192.168.15.10',master_user='slave001',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=108; mysql> start slave; mysql> show slave statusG ...
确认从库正常启动后,回到主库执行unlock tables。
3.验证
在master上创建test2库。
root@master:/# mysql -uroot -p mysql> create database test2;
在slave上查看test2库是否存在。
root@slave:/# mysql -uroot -p mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test2 | +--------------------+
4.常见问题
主库同步数据给从库时如果发生数据不合法情况,从库同步线程会自动终止。这时需要解决不合法问题后再start slave,通过日志可以发现该问题。