MySQL主从同步
普通文件的数据同步
1.NFS网络文件共享可以同步存储数据。
2.samba共享数据。
3.定时任务或守护进程结合rsync，scp。
4.inotify（sersync）+rsync触发时数据同步
5.ftp数据同步
6.ssh key + scp/rsync
7.svn版本管理
8.rsync，sersync，inotify，union（双向同步），csync2（多向同步）。
MySQL主从复制
MySQL支持单向、双向、链式级联、实时、异步复制。在复制过程中，一台服务器充当主服务器（master），而一个或多个其它的服务器充当从服务器 （Slave）。
复制可以是单向：M-->S，也可以是多向:M<==>M，也可以多M环状同步。
当前生产工作中，大多数应用的MySQL主从同步都是异步的复制方式，不是严格实时的数据同步。
实时和异步：
配置好主从同步后，所有对数据库内容的更新就必须在主服务器上进行。避免用户对主服务器上数据库内容的更新与对从服务器上数据库内容的更新不一致导致发生冲突。
主从复制应用场景：
MySQL主从复制有利于数据库架构的健壮性，提升访问速度和易于维护管理。
1.主从服务器互为备份。
2.主从服务器读写分离分担网站压力。
实现：通过程序（java，php）。测试：代理软件（mysql-proxy,amoeba）
门户网站：分布式dbproxy（读写分离、hash负载均衡、健康检查）。
处理主库出问题：
1.一台从库，把主库的binlog拉过来补全。
2.双写（程序双写）。
3.应用程序写log（一分钟）。
4.半同步插件（实时）。
主从同步方案：
MySQL主从同步原理
Slave两个线程：IO线程、SQL线程
Master一个线程：IO线程
要完成主从同步，主库必须开启binlog。
从库找主库：change master to(master_host、master_user...)
开启同步：在从库上start slave
1.Master有一个线程，Slave有两个线程来完成。
2.配置连接的信息（用户名、密码）。
3.在文件位置点前确保主从数据一致。
4.开启前，在主库建立连接从库的账号。
5.主库需要打开binlog。
6.从库打开的过程就是让IO线程和SQL线程开始工作。
主从同步实践
定义服务器角色
主库（MySQL Master）：ip为localhost port为3306
从库（MySQL Salve）：IP为localhost port为3307
1.一般常规主从复制，主从服务器在不同的机器上，并且监听的端口均默认为3306。
2.下面以单数据库多实例为例。
数据库环境准备
1.单机单数据库多实例的环境。
2.两台服务器每个机器一个数据库的环境。
数据库读法约定
主库：称为Master
从库：称为Salve
主库上操作
1.打开配置文件中mysqld_3306中的log-bin。
2.确定server_id为唯一。
# 检查log-bin和server_id是否打开 [root@localhost ~]# egrep "log-bin|server_id" /etc/my.cnf server_id=3306 log-bin = /data/mysql/mysql_3306/log/mysql3306_bin server_id=3307 log-bin = /data/mysql/mysql_3307/log/mysql3307_bin # server_id = ..... [root@localhost ~]#
建立用于同步的账号rep
登陆mysql 3306实例数据库
[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock
建立用于从库复制的账号rep
mysql> grant replication slave on *.* to 'rep'@'192.168.150.%' identified by '123456'; Query OK, 0 rows affected (0.34 sec) --replication slave为mysql同步的必须权限 --*.* 表示所有库所有表 --'rep'@'192.168.150.%' 为同步账号
刷新
mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
为主库加读的锁
mysql> flush table with read lock;
查看
mysql> show master status; +----------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------------+----------+--------------+------------------+-------------------+ | mysql3306_bin.000007 | 408 | | | | +----------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> show master logs; +----------------------+-----------+ | Log_name | File_size | +----------------------+-----------+ | mysql3306_bin.000001 | 143 | | mysql3306_bin.000002 | 143 | | mysql3306_bin.000003 | 806 | | mysql3306_bin.000004 | 1203 | | mysql3306_bin.000005 | 2809 | | mysql3306_bin.000006 | 8535 | | mysql3306_bin.000007 | 408 | +----------------------+-----------+ 7 rows in set (0.00 sec)
重新开启一个窗口进行备份
[root@localhost ~]# mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock -A -B --events --master-data=2 >/opt/rep.sql
查看备份文件 搜素CHANGE。与show master status进行对比。
vim /opt/rep.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql3306_bin.000007', MASTER_LOG_POS=408;
恢复锁表
mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)
从库上操作
将主库上数据上传到从库上
[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3307.sock </opt/rep.sql
登陆到从库上
[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3307.sock # 执行change命令 mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.150.6', -> MASTER_PORT=3306, -> MASTER_USER='rep', -> MASTER_PASSWORD='123456', -> MASTER_LOG_FILE='mysql3306_bin.000007', -> MASTER_LOG_POS=408; Query OK, 0 rows affected, 2 warnings (0.37 sec) # 主库进行备份时用参数 --master-data=1 master_log_flie和master_log_pos可以不写。
上面的数据放在从库的maste.info文件中，进入到datadir目录下进行查看
[root@localhost mysql]# cd /data/mysql/mysql_3307/data [root@localhost data]# ls auto.cnf db_3306 ib_logfile1 localhost-relay-bin.index performance_schema db1 ibdata1 localhost.pid master.info relay-log.info db2 ib_logfile0 localhost-relay-bin.000001 mysql [root@localhost data]# cat master.info 23 mysql3306_bin.000007 408 192.168.150.6 rep 123456 3306 60 0 0 1800.000 0 86400 0 [root@localhost data]#
开启开关
mysql> start slave; Query OK, 0 rows affected (0.04 sec)
查看是否成功，一般Slave_IO_Runing:Yes\Slave_SQL_Runing:Yes两个都是yes即成功
mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.150.6 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql3306_bin.000007 Read_Master_Log_Pos: 408 Relay_Log_File: localhost-relay-bin.000002 Relay_Log_Pos: 287 Relay_Master_Log_File: mysql3306_bin.000007 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: 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: 408 Relay_Log_Space: 464 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: 3306 Master_UUID: 7bc41a19-f19a-11e8-84fb-000c29f0c71b Master_Info_File: /data/mysql/mysql_3307/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec) mysql>
SQL线程会产生relay-log.info（中继日志）。在从库的datadir目录下。
测试
在主库上创建库进行测试
[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.6.22-log MySQL Community Server (GPL) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | db_3306 | | mysql | | performance_schema | | test | +--------------------+ 7 rows in set (0.00 sec) mysql> create database db_test; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | db_3306 | | db_test | | mysql | | performance_schema | | test | +--------------------+ 8 rows in set (0.00 sec) mysql>
在从库上查看
[root@localhost data]# mysql -uroot -p123456 -S /tmp/mysql_3307.sock Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.6.22-log MySQL Community Server (GPL) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | db_3306 | | db_test | | mysql | | performance_schema | | test | +--------------------+ 8 rows in set (0.01 sec) mysql>
MySQL主从同步配置步骤
1.准备两台数据环境，或者单台多实例环境，能正常启动和登陆。
2.配置my.cnf文件，主库配置log-bin和server_id参数，从库配置server_id,一般不开启从库log-bin。配置后重启生效。
3.登陆主库增加用于从库连接主库同步的账号。授权replication slave同步的权限。
4.登陆主库，整库锁表flush table with read lock（关闭窗口失效，参数超时）；show master status查看binlog位置状态。
5.新开窗口，linux命令行备份或导出原有的数据库数据，并拷贝到从库所在的服务器目录。
6.解锁主库：unlock tables;
7.把主库导出的原有数据恢复到从库。
8.根据主库的show master status查看binlog的位置状态，在从库执行change master to...语句。
9.从库开启同步开关，start slave。
10.从库show slave status \G，检查同步状态，并在主库进行更新测试。
生产环境快速配置MySQL主从复制方案
1.安装好要配置从库的数据库，配置好log-bin和server-id参数。
2.无需配置主库my.cnf文件，主库的log-bin和server-id参数默认时配置好的。
3.登陆主库增加用于从库连接主库同步的账号。授权replication slave同步的权限。
4.使用半夜mysqldump带--master-data=1备份的全备数据恢复到从库。
5.在从库执行change master to... 语句，无需binlog文件及对应位置点。
6.从库开启同步开关，start slave 。
7.从库show slave status \G，检查同步状态，并在主库进行更新测试。
主从复制线程状态
具体参数可以在mysql参考手册查询
复制主线程状态
下面为主服务器的Binlog Dump线程的State列的最常见状态。若无，说明复制没有运行，没有连接从服务器。
Sending binlog event to slave
二进制日志由各种事件组成，一个事件通常为一个更新加一些其它信息。
线程已经从二进制日志读取了一个事件并且正将它发送到从服务器。
Finished reading one binlog；switching to next binlog；
线程已经读完二进制日志文件并且正打开下一个要发送到从服务器的日志文件。
Has sent all binlog to slave；waiting for binlog to be updated
线程已经从二进制日志读取所有主要的更新并已经发送到了从服务器。
线程正在空闲，等待由主服务器上新的更新导致的出现在二进制日志中的新事件。
Waiting to finalize termination
线程停止时发生的一个很简单的状态。
复制从I/O线程状态
该状态出现在Slave_IO_State列，由show slave status显示。
Connecting to master
线程正试图连接主服务器
Checking master version
建立同主服务器之间的连接后立即临时出现的状态。
Registering slave on master
...
复制从SQL线程状态
Reading event from the relay log
线程已经从中继日志读取一个事件，可以对事件进行处理了。
Has read all relay log；waiting for the slave I/O thread to update it
线程已经处理了中继日志文件中的所有事件，现在正等待I/O线程将新事件写入中继日志。
Waiting for slave mutex on exit
线程停止时发生的一个很简单的状态。
生产场景MySQL主从复制读写分离授权方案及实战
当配置好MySQ主从复制后，所有对数据内容的更新必须在主服务器上面执行。
读写分离方案：
1.生产授权
主库：select、insert、delete、update
从库：select （主库的用户同步到从库，然后回收除select的权限）
2.通过read-only参数防止数据写从库的方法
read-only参数可以让slave服务器只允许来自slave服务器线程或具有super权限的用户更新，可以确保slave服务器不接受来自普通用户的更新，slave服务器启动选项增加--read-only也是同样功能。
read-only读写分离步骤：
1.在my.cnf中3307[mysqld]下面添加参数read-only。
2.重启3307数据库。
[root@localhost ~]# vi /etc/my.cnf [root@localhost ~]# killall mysqld [root@localhost ~]# /usr/local/mysql/bin/mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld3306 is not running MySQL server from group: mysqld3307 is not running [root@localhost ~]# /usr/local/mysql/bin/mysqld_multi start [root@localhost ~]# /usr/local/mysql/bin/mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld3306 is running MySQL server from group: mysqld3307 is running
3.创建一个新的用户
4.连接新用户并进行测试，read-only模式下不能插入数据。
[root@localhost ~]# mysql -utest -p123456 -S /tmp/mysql_3307.sock mysql> user test; mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | tb2 | +----------------+ 1 row in set (0.00 sec) mysql> select * from tb2; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | d | +----+------+ 3 rows in set (0.00 sec) mysql> insert into tb2(id,name) values(4,'e'); ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement mysql>
主从复制故障
1 MySQL从数据库冲突导致同步停止
模拟
[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3307.sock mysql> show slave status\G mysql> create database out_sync; Query OK, 1 row affected (0.00 sec) mysql> show databases; # 此时还是同步状态 mysql> show slave status\G # 回到主库执行命令 [root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock mysql> create database out_sync; mysql> show databases;
回到从库查看状态
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.150.6 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql3306_bin.000009 Read_Master_Log_Pos: 1427 Relay_Log_File: localhost-relay-bin.000008 Relay_Log_Pos: 1488 Relay_Master_Log_File: mysql3306_bin.000009 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1007 Last_Error: Error 'Can't create database 'out_sync'; database exists' on query. Default database: 'out_sync'. Query: 'create database out_sync' Skip_Counter: 0 Exec_Master_Log_Pos: 1321 Relay_Log_Space: 1938 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1007 Last_SQL_Error: Error 'Can't create database 'out_sync'; database exists' on query. Default database: 'out_sync'. Query: 'create database out_sync' Replicate_Ignore_Server_Ids: Master_Server_Id: 3306 Master_UUID: 7bc41a19-f19a-11e8-84fb-000c29f0c71b Master_Info_File: /data/mysql/mysql_3307/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 181203 11:42:05 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec) mysql>
1007：数据库已经存在，创建数据库失败。
解决方法：
mysql> stop slave; Query OK, 0 rows affected (0.36 sec) mysql> set global sql_slave_skip_counter=1; Query OK, 0 rows affected (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status \G
根据错误号跳过指定的错误，可以写在配置文件中。
slave-skip-errors=1032,1062,1007
MySQL从库记录binlog日志
需要记录binlog的情况：
1.当前从库还要作为其它从库的主库。
2.把从库作为备份服务器时需要开启binlog。
参数：
lob-bin
log-slave-updates
expire_logs_days=7
主库宕机
1.在从库查看 show processlist \G
2.查看master.info看那个靠前，选pos最大的作为主库。
切换：
1.确保所有relay log全部更新完毕。
在每个从库上执行stop slave io_thread:show processlist;
直到看到Has read all relay log;表示从库更新都执行完毕。
2.登陆：mysql -uroot -p123456 -S /tmp/mysql_3307.sock
stop slave;
retset master;
quit;
3.进到数据库数据目录，删除master.info relay-log.info，检查授权表
4.提升从库为主库
vi /etc/my.cnf
开启log-bin，注释log-slave-updates\read-only
5.其它从库操作
登陆从库
stop slave;
change master to ...
start slave;
