MySQL主从复制

原理

　　MySQL主从复制通过两台机器，实现一台机器数据的变化在另一台机器上实时同步的过程。

　　首先开启master数据库和slave的binlog文件，再给master创建slave允许复制的用户。当master数据库获取到更改数据库的SQL语句时，master数据库会将更改数据库的SQL保存至binlog文件，运行在slave数据库上的IO Thread线程根据position位置记录读取binlog文件，从而写入relay log文件，之后再由slave的SQL Thread线程读取relay log文件并执行SQL以达到数据同步。

最佳实践

　　在生产环境下的单数据库在不关闭服务下做主从复制。

1、首先需要将单数据库修改为master库

[root@localhost ~]# vi /etc/my.cnf    #追加一下内容到my.cnf并保存
server-id=1
log-bin=/var/lib/mysql/mysql-bin
[root@localhost ~]# service mysqld restart

• server-id：表示数据库的标识
• log-bin：表示binlog文件存放地址

2、将master库的数据备份

[root@localhost ~]# mysqldump -uroot -p --single-transaction --master-data=2 --triggers --routines --all-databbases > ~/master.sql

• mysqldump：数据库备份工具，由MySQL提供
• uroot：表示用户为root
• p：表示密码
• single-transaction：表示设置事务的隔离级别为可重复读，也就是MySQL数据库的默认级别
• master-data：表示binlog信息，1为记录binlog和pos点  2为记录binlog和pos点，但注释这行
• triggers：表示保存触发器
• routines：表示保存存储过程和自定义函数
• all-databases：表示导出所有的库
• master.sql：表示导出文件名

3、拷贝SQL文件到从服务器

[root@localhost ~]# scp root@172.16.68.109:/root/abc.sql ~

4、导入slave中

[root@localhost ~]# mysql -uroot -p < ~/abc.sql

5、在master库中授复制的权限给slave

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slaverepl'@'192.168.1.124' IDENTIFIED BY 'slave';
mysql> FLUSH PRIVILEGES;

6、查看master状态，并记录binlog文件名和pos位置

mysql> SHOW MASTER STATUS;

7、接下来设置slave的配置

[root@localhost ~]# vi /etc/my.cnf    #追加一下内容到my.cnf并保存
server-id=2
log-bin=/var/lib/mysql/mysql-bin
[root@localhost ~]# service mysqld restart

8、如果这台机器之前做过主从复制，清理一下relay-log文件

mysql> RESET SLAVE;

9、在slave中执行change master语句

mysql> CHANGE MASTER TO master_to='192.168.1.125',master_user='slaverepl',master_password='slave',master_log_file='mysql-bin.000001',master_log_pos=154;

这里的master_log_file就是上面记录的binlog文件名，master_log_pos就是上面记录的pos值。

10、启动slave，并查看状态

mysql> START SLAVE;
mysql> SHOW SLAVE STATUS;

如果看到IO Thread的值和SQL Thread的值都为Yes就说明主从复制成功。

接下来在master库添加一条数据，在slave库也同时可以看见这条数据。