正文
Linux下mysql的主从复制配置
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
1.准备两台数据库环境，或者单台多实例环境，能正常启动和登录。
数据库的安装和多实例配置请参考https://www.cnblogs.com/qiuhom-1874/p/9757061.html。
2.配置my.cnf文件
[root@qiuhom 3306]# egrep "log-bin|log_slave_update|server-id" ../3306/my.cnf
log-bin = /mysql_multi_case/3306/mysqld-bin
server-id = 1
[root@qiuhom 3306]# egrep "log-bin|log_slave_update|server-id" ../3307/my.cnf
log-bin = /mysql_multi_case/3307/mysqld-bin
log_slave_updates = 1
server-id = 2
[root@qiuhom 3306]# egrep "log-bin|log_slave_update|server-id" ../3308/my.cnf
#log-bin = /mysql_multi_case/3308/mysqld-bin
server-id = 3
主库配置log-bin和server-id参数，从库配置server-id，不能和主库相同以及其他从库相同，一般不开启log-bin功能。除非从库级联要开启log-bin,从库级联除了开启log-bin 还需要开启log-slave-updates = 1
注意：更改my.cnf配置，需要重启服务才生效。
3.登录主库增加用于从库连接主库同步的账号， 例如rep,并授权replication slave同步权限。
mysql> grant replication slave on *.* to 'rep'@'10.0.0.39' identified by 'admin';
Query OK, 0 rows affected (0.00 sec) mysql> select user,host,password from mysql.user where user='rep';
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| rep | 10.0.0.39 | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
+------+-----------+-------------------------------------------+
1 row in set (0.00 sec) mysql> show grants for rep@'10.0.0.39';
+------------------------------------------------------------------------------------------------------------------------+
| Grants for rep@10.0.0.39 |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'rep'@'10.0.0.39' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441' |
+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
说明：有关mysql数据库创建用户和授权请参考：https://www.cnblogs.com/qiuhom-1874/p/9741166.html
4.登录主库，整库锁表flush table with read lock。(窗口关闭即失效，超时参数到了也失效)，然后show master status;查看binlog日志文件名和位置状态。(mysql5.1 锁库是 flush tables with read lock多个s)
mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec) mysql> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000001 | 653 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
说明：锁表的目的是防止我们查看binlog日志文件和位置点时，数据还在往库里写。锁表能更好的，更准确的记录binlog日志文件和位置点。同时为备份做好了准备，锁表能准确的记录备份时binlog的文件和位置点。这里还是要说下binlog二进制日志文件，这个文件主要记录着对mysql数据库的数据有更新的操作的语句（增删改），千万记住查询它不记录，所以我们锁表的目的就在这里，防止我们在查看binlog日志文件和位置点时，写入数据导致我们找到文件和位置点和实际的不同。
5.新开窗口，Linux 命令行备份和导出原有的数据库数据
[root@qiuhom ~]# mysqldump -uroot -padmin -S /mysql_multi_case/3306/mysql.sock -A --events > /work/bak/all.sql
说明：有关mysql备份请参考：https://www.cnblogs.com/qiuhom-1874/p/9747219.html 这里要说下 --master-data=1 这个选项的主要作用是我们备份的时候在备份sql语句里记录binlog日志文件和位置点，这个选项有个很好的好处就是我们不用锁表就能准确的拿到备份是的binlog日志文件名和位置点，同时我们在做主从的时候从库不需要指定binlog日志文件和位置的，因为备份出来sql语句有告诉从库binlog日志和位置点。--master-data=2 这个和等于1的选项只有一个区别就是等于2是把binlog日志和位置点的语句给注释了的，等于1的是没有注释的，推荐备份使用这个选项。
如果数据量很大，我们建议申请停机备份时间，直接打包数据文件。
6.解锁主库，unlock tables;
mysql> unlock table;
Query OK, 0 rows affected (0.00 sec)
提示：如果备份用--master-data选项锁表和解锁这两步都可以省略。
7.把主库导出的原有数据恢复到从库。
[root@qiuhom ~]# mysql -uroot -padmin -S /mysql_multi_case/3307/mysql.sock </work/bak/all.sql
提示：因为是全备 所有不需要指定库。
8.根具主库的show master status;查看binlog的位置状态，在从库执行change master to ...语句
CHANGE MASTER TO
MASTER_HOST='10.0.0.39',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='admin',
MASTER_LOG_FILE='mysqld-bin.000001',
MASTER_LOG_POS=653;
提示：如果全备是有给定选项--master-data=1 那么我们在从库可以不用写MASTER_LOG_FILE='mysqld-bin.000001',和MASTER_LOG_POS=653;这两参数。
9.从库开启同步开关，start slave;
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
10.从库检查同步状态，并在主库进行更新测试。show slave status\G;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.39
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000001
Read_Master_Log_Pos: 653
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 647
Relay_Master_Log_File: mysqld-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 653
Relay_Log_Space: 797
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
1 row in set (0.00 sec)
提示：我们可以从返回的信息看到Slave_IO_Running: Yes和Slave_SQL_Running: Yes。io和sql线程已经启动了，说明主从已经配置好了。当然我们也可以看线程状况。
mysql> show processlist;
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
| 4 | system user | | NULL | Connect | 29932 | Waiting for master to send event | NULL |
| 5 | system user | | NULL | Connect | 2584 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 11 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
我们可以看到当前两个线程的状态。
mysql> show processlist;
+----+----------+-----------------+------+-------------+-------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+-----------------+------+-------------+-------+-----------------------------------------------------------------------+------------------+
| 5 | rep | 10.0.0.39:36750 | NULL | Binlog Dump | 30068 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 23 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+----------+-----------------+------+-------------+-------+-----------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
在主库上看有个我们创建的用户连到主库上，也可看到当前io线程的状态。
接下来我们就可以在主库上测试创建库，看在从库上是否同步成功
[root@qiuhom ~]# mysql -uroot -padmin -S /mysql_multi_case/3306/mysql.sock -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
[root@qiuhom ~]# mysql -uroot -padmin -S /mysql_multi_case/3307/mysql.sock -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
[root@qiuhom ~]# mysql -uroot -padmin -S /mysql_multi_case/3306/mysql.sock -e "create database abcd;use abcd;create table test(id int);"
[root@qiuhom ~]# mysql -uroot -padmin -S /mysql_multi_case/3306/mysql.sock -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| abcd |
| mysql |
| performance_schema |
+--------------------+
[root@qiuhom ~]# mysql -uroot -padmin -S /mysql_multi_case/3307/mysql.sock -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| abcd |
| mysql |
| performance_schema |
+--------------------+
[root@qiuhom ~]# mysql -uroot -padmin -S /mysql_multi_case/3307/mysql.sock -e "use abcd;show tables;"
+----------------+
| Tables_in_abcd |
+----------------+
| test |
+----------------+
可以看出我们在主库上创建库表能及时的同步到从库上，说明主从复制是配置成功的。说下原理吧，主从复制首先它是异步处理的过程，怎么说呢，从上面我们查看线程list可以看出主库上有一个io线程，从库上有一个io线程和sql线程，首先从库会对master-info里的用户名和密码并向向主库发送连接同步认证，主库主进程收到认证消息，它就把这个任务直接转交给主库的io线程，认证成功后从库的io线程就会去读master-info里的记录的binlog文件名和对应的位置点发给主库的io线程，主库收到这个信息后就把对应的日志文件和位置点以下的内容发给从库的io线程，从库io线程收到数据后就把收到的数据写进中继日志文件relay-log，同时也会更新master-info里的binlog文件名和位置点，到此从库的io就不管了，它主要的作用就是把主库io线程发过来的binlog日志写进中继日志和更新master-info。接下来sql线程就来读中继日志relay-log里的内容，sql线程它会做两个事情，一是把读到的sql语句在本地执行并生成数据文件。第二件事就是他会把它执行的sql语句位置点记录到relay-info的文件，relay-info记录sql线程把binlog应用到本地数据库里的位置点,sql线程会不断的去读relay-info里记录的binlog文件名和位置点，并拿着这些信息去relay-log里找对应的位置的sql语句，并把这些sql语句执行生成数据文件。sql线程每读一次relay-log，它就会往relay-info里记录它读到什么位置，然后执行sql语句生成数据文件。
mysql主从复制的原理大概主要有以下几点：
1.异步方式同步。（slave端的io线程不会等sql线程把语句执行了后再向master端发送binlog位置点，slave端的io线程会不断的读master-info里的binlog文件名和位置点发送给master端，至于sql线程他们俩不是同步处理的）
2.逻辑同步模式。多种模式，默认是通过sql语句执行。
3.主库通过记录binlog实现对从库的同步。binlog记录数据库更新语句（增删改）。
4.主库1个线程，从库2个线程来完成的。（主io，从io,sql）
5.从库关键文件master.info（记录change master 信息） ,relay-log（中继日志，记录主库io线程发过来的binlog日志）,relay-info（记录sql线程把binlog应用到本地数据库里的位置点）功能。
6.如果从库级联，需要打开log-bin和log-slave-updates选项
