MySQL 主从复制

 

1 复制概述

      Mysql内建的复制功能是构建大型，高性能应用程序的基础。将Mysql的数据分布到多个系统上去，这种分布的机制，是通过将Mysql的某一台主机的数据复制到其它主机（slaves）上，并重新执行一遍来实现的。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。

请注意当你进行复制时，所有对复制中的表的更新必须在主服务器上进行。否则，你必须要小心，以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。

1.1 mysql支持的复制类型：

　　（１）：基于语句的复制：  在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。  
            一旦发现没法精确复制时，   会自动选着基于行的复制。    
　　（２）：基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持
　　（３）：混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

1.首先在主库上执行操作

1.1 设置server-id值并开启binlog参数

 根据前文MySQL 的主从同步原理，我们知道要实现主从复制，关键因素就是开启binlog日志功能，所以，我们首先打开主库的binlog日志参数。

1.2实现主从复制的必要条件：

(1)在配置文件my.cnf中配置server-id不能一样。

(2)在主库的配置文件my.cnf中开启log-bin。

查询是否开启代码如下：

 

1. [root@mysql ~]# egrep "log-bin|server-id"/data/{3306,3307}/my.cnf
2. /data/3306/my.cnf:log-bin =/data/3306/mysql-bin ##显示已经开启
3. /data/3306/my.cnf:server-id =1
4. /data/3307/my.cnf:#log-bin =/data/3307/mysql-bin
5. /data/3307/my.cnf:server-id =3
6. [root@mysql ~]#

如果未开启修改主库的配置文件

执行vim /data/3306/my.cnf,编辑多实例3306的my.cnf配置文件，两个参数按如下内容修改：

提示：

1.上面两参数要放在my.cnf中的[mysqld]模块下，否则会出错。

1.3.修改完配置文件后，重启数据库

1. [root@mysql ~]#/data/3306/mysql stop
2. StopingMySQL...
3. [root@mysql ~]#/data/3306/mysql start

1.4.我们也可以在登录数据库中，查看修改的两处配置是否生效；

使用show variables;查看mysql究竟配置了什么参数；

1. mysql> show variables like "log_bin";
2. +---------------+-------+
3. |Variable_name|Value|
4. +---------------+-------+
5. | log_bin | ON |
6. +---------------+-------+
7. 1 row in set (0.00 sec)
9. mysql> show variables like "server_id";
10. +---------------+-------+
11. |Variable_name|Value|
12. +---------------+-------+
13. | server_id |1|
14. +---------------+-------+
15. 1 row in set (0.00 sec)

2.1 建立用于主从复制的账号

 根据主从复制的原理，从库要想和主库同步，必须有一个可以连接主库的账号，并且这个账号的权限是主库上创建的，权限是允许主库的从库连接并同步数据。

 

 

2.1、登录mysql3306 实例主数据库

1. [root@mysql ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock

2.2、建立用于从库复制的账号rep:

1. mysql> grant replication slave on *.* to 'rep'@'192.168.1.%' identified by '123456';
2. Query OK,0 rows affected (0.00 sec)

2.3、查看授权

1. mysql> drop user 'root'@'mysql';##为安全考虑，将无用的授权删除
2. Query OK,0 rows affected (0.02 sec)
4. mysql> select user,host from mysql.user;
5. +------+-------------+
6. | user | host |
7. +------+-------------+
8. | root |127.0.0.1|
9. | rep |192.168.1.%|
10. | root | localhost |
11. +------+-------------+
12. 3 rows in set (0.00 sec)
13. mysql> flush privileges; ##刷新权限 Query OK, 0 rows affected (0.00 sec)


2.4 查看我们做过授权的用户，所具备的权限

2.5 给数据库一个读锁，是的用户无法创建库，只能读：

1. mysql> flush table with read lock; ##将数据库表锁起来，锁表窗口不能退出，否则锁表失效
2. Query OK,0 rows affected (0.00 sec)
4. mysql> create database zhu;
5. ERROR 1223(HY000):Can't execute the query because you have a conflicting read lock

 

2.6记录锁表的位置点

1. mysql> show master status;##查看锁表的位置点
2. +------------------+----------+--------------+------------------+
3. |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
4. +------------------+----------+--------------+------------------+
5. | mysql-bin.000004|894|||
6. +------------------+----------+--------------+------------------+
7. 1 row in set (0.00 sec)
9. mysql>

 

2.7 锁表打包数据库

1. [root@mysql ~]# mysqldump -uroot -p123456 --events -S /data/3306/mysql.sock -A -B|gzip >/opt/bak_$(date +%F).sql.gz
2. [root@mysql ~]# ll /opt/
3. 总用量148
4. -rw-r--r--.1 root root 1443244月900:13 bak_2016-04-09.sql.gz

2.8 锁表打包完毕后，检查位置点有无变化，如果有变化，表示在打包的过程中有数据写入，锁表无效

1. mysql> show master status;##锁表后位置点事894
2. +------------------+----------+--------------+------------------+
3. |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
4. +------------------+----------+--------------+------------------+
5. | mysql-bin.000004|894|||
6. +------------------+----------+--------------+------------------+
7. 1 row in set (0.00 sec)
9. mysql> show master status;##锁表打包后，位置点没有变化，表示打包数据完成
10. +------------------+----------+--------------+------------------+
11. |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
12. +------------------+----------+--------------+------------------+
13. | mysql-bin.000004|894|||
14. +------------------+----------+--------------+------------------+
15. 1 row in set (0.00 sec)

2.9 接着将锁表打开

1. mysql> unlock tables;
2. Query OK,0 rows affected (0.00 sec)

1. mysql> create database zhu;##解锁以后可以写数据
2. Query OK,1 row affected (0.03 sec)
4. mysql>

 

2.9.1 查看生成的binlog文件

2.9.2 使用mysqlbinlog命令查看生成的binlog日志，mysqlbinlog 专门是将二进制语句翻译成sql语句的工具：

1. [root@mysql 3306]# mysqlbinlog mysql-bin.000004

3.接着做恢复从库的工作

1. [root@mysql opt]# cd /opt/
2. [root@mysql opt]# ll
3. 总用量664
4. -rw-r--r--.1 root root 5282954月900:50 all-tmp.sql
5. -rw-r--r--.1 root root 1443244月900:13 bak_2016-04-09.sql.gz
6. drwxr-xr-x.2 root root 40963月262015 rh
7. [root@mysql opt]# gzip -d bak_2016-04-09.sql.gz
8. [root@mysql opt]# mysql -uroot -p123456 -S /data/3307/mysql.sock <bak_2016-04-09.sql
9. [root@mysql opt]#

 

3.1.1 接着在从库上执行change master to命令，实质实在从库上生成master.info文件的过程

1. [root@mysql opt]# mysql -uroot -p123456 -S /data/3307/mysql.sock<<EOF
2. stop slave;
3. CHANGE MASTER TO
4. MASTER_HOST='192.168.1.31',
5. MASTER_PORT=3306,
6. MASTER_USER='rep',
7. MASTER_PASSWORD='123456',
8. MASTER_LOG_FILE='mysql-bin.000004',
9. MASTER_LOG_POS=894;
10. EOF

 

3.1.2 最后一步开启从库开关：

1. mysql> start slave; ##打开开关
2. Query OK,0 rows affected (0.00 sec)
4. mysql>

接着检查从库状态信息：

1. mysql> show slave status \G;
2. ***************************1. row ***************************
3. Slave_IO_State:Waitingfor master to send event
4. Master_Host:192.168.1.31
5. Master_User: rep
6. Master_Port:3306
7. Connect_Retry:60
8. Master_Log_File: mysql-bin.000004
9. Read_Master_Log_Pos:975
10. Relay_Log_File: relay-bin.000002
11. Relay_Log_Pos:334
12. Relay_Master_Log_File: mysql-bin.000004
13. Slave_IO_Running:Yes
14. Slave_SQL_Running:Yes
15. Replicate_Do_DB:
16. Replicate_Ignore_DB: mysql
17. Replicate_Do_Table:
18. Replicate_Ignore_Table:
19. Replicate_Wild_Do_Table:
20. Replicate_Wild_Ignore_Table:
21. Last_Errno:0
22. Last_Error:
23. Skip_Counter:0
24. Exec_Master_Log_Pos:975
25. Relay_Log_Space:484
26. Until_Condition:None
27. Until_Log_File:
28. Until_Log_Pos:0
29. Master_SSL_Allowed:No
30. Master_SSL_CA_File:
31. Master_SSL_CA_Path:
32. Master_SSL_Cert:
33. Master_SSL_Cipher:
34. Master_SSL_Key:
35. Seconds_Behind_Master:0
36. Master_SSL_Verify_Server_Cert:No
37. Last_IO_Errno:0
38. Last_IO_Error:
39. Last_SQL_Errno:0
40. Last_SQL_Error:
41. Replicate_Ignore_Server_Ids:
42. Master_Server_Id:1
43. 1 row in set (0.00 sec)

3.1.3 最后测试主从

 

主库创建库：

从库：

 

最佳方案：

4.1 无须锁表的mysqldump备份命令

      myisam 引擎企业生产备份命令：

mysqldump -uroot -p123456 -A -B --mastet-data=2 -x --events|gzip >/opt /all.sql.gz

      innodb 引擎企业生产备份命令：推荐使用的

mysqldump -uroot -p123456    -A     -B     -F     --mastet-data=2    --events   --single-transaction|gzip   >/opt/all.sql.gz

 

1. [root@mysql opt]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B --master-data=2-x --events >/opt/all-tmp.sql ##演示如下命令
2. [root@mysql opt]# vim all-tmp.sql
3. 其中--master-data=2,表示在前面加--
   
   
4. -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=975;##记录了增量备份的起点位置，主要是参数--master-data=2起的作用

 

注：

--master-data作用：

1、使用--master-data=2 进行备份文件会增加如下内容：适合普通备份增量恢复