MySQL主从复制实战

arongsoft

　　一、复制准备
　　(1)定义机器角色：
　　主库    192.168.11.34 port 3306
　　[root@master ~]# cat /etc/sysconfig/network
　　HOSTNAME=master
　　从库    192.168.11.35 port 3306
　　[root@slave ~]#cat /etc/sysconfig/network
　　HOSTNAME=slave
　　提示：一般做主从，主从服务器在不同的机器上，监听端口为3306。可以根据实际情况进行修改。
　　(2)主库上执行操作
　　设置server-id值并开启binlog设置
　　根据mysql的同步原理，我们知道复制的关键因素就是binlog日志。
　　执行[root@master ~]# vim /etc/my.cnf，编辑my.cnf配置文件，添加两个参数
　　[mysqld]
　　server_id = 34
　　log_bin =/usr/local/data/mysql-bin
　　注意：上述两个参数必须放在my.cnf配置文件[mysqld]中，否则报错。
　　server_id = 34可以是服务器IP后以为，避免重复。
　　修改配置文件后需要重启mysql。
　　[root@master data]# grep -E "server-id|log-bin" /etc/my.cnf  检查配置结果
　　server-id = 34
　　log-bin = /usr/local/data/mysql-bin
　　(3)建立用于从库复制的账号tangbo

　　mysql> grant replication slave on *.* to 'tangbo'@'192.168.11.%'>　　Query OK, 0 rows affected (0.00 sec)
　　mysql>replication slave 为mysql同步必须权限，此处不要授权all。*.*表示所有库所有表。
　　查看已经创建的用户
　　mysql> select user,host from mysql.user;
　　+--------+---------------+
　　| user   | host          |
　　+--------+---------------+
　　| root   | 127.0.0.1     |
　　| tangbo | 192.168.11.%
　　(4)锁表只读（当前窗口不要关掉）
　　生产环境执行锁表操作时，会影响业务。   需申请停机时间
　　mysql> flush tables with read lock;
　　Query OK, 0 rows affected (0.00 sec)
　　mysql>
　　mysql> create database test1;
　　ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock 创建不了库，锁表成功
　　mysql> select * from renchuan.caiwu;   执行查询无任何问题
　　+----+--------+------+----------+------+------------+

　　|>　　+----+--------+------+----------+------+------------+
　　|  1 | tangbo | 27   | wangguan | 3000 | 2015-12-20 |
　　+----+--------+------+----------+------+------------+
　　1 row in set (0.00 sec)
　　mysql>
　　提示：这个锁表命令的时间，在不同引擎的情况，有可鞥受如下参数的控制。锁表时，如果超过设置时间不操作会自动解锁。
　　interactive_timeout = 60
　　wait_timeout = 60
　　默认时常为：
　　mysql> show variables like '%timeout%';
　　+-----------------------------+----------+
　　| Variable_name               | Value    |
　　+-----------------------------+----------+
　　| connect_timeout             | 10       |
　　| delayed_insert_timeout      | 300      |
　　| innodb_flush_log_at_timeout | 1        |
　　| innodb_lock_wait_timeout    | 50       |
　　| innodb_rollback_on_timeout  | OFF      |
　　| interactive_timeout         | 28800    |
　　| lock_wait_timeout           | 31536000 |
　　| net_read_timeout            | 30       |
　　| net_write_timeout           | 60       |
　　| rpl_stop_slave_timeout      | 31536000 |
　　| slave_net_timeout           | 3600     |
　　| wait_timeout                | 28800    |
　　+-----------------------------+----------+
　　12 rows in set (0.00 sec)
　　mysql>
　　(5)查看主库状态  即查看当前日志文件名和二进制偏移量
　　mysql> show master status;命令显示的信息要记录下来，后面从库的复制是从这个位置开始的。
　　mysql> show master status;
　　+------------------+----------+--------------+------------------+-------------------+
　　| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
　　+------------------+----------+--------------+------------------+-------------------+
　　| mysql-bin.000013 |      331 |              |                  |                   |
　　+------------------+----------+--------------+------------------+-------------------+
　　1 row in set (0.00 sec)
　　mysql>
　　(6)新开窗口，导出数据库数据，如果量很大（+100G），且可以停机，直接打包数据迁移。
　　[root@master data]# mkdir /server/backup/ -p  创建备份目录
　　[root@master /]# mysqldump -uroot -ptangbo -A -B|gzip > /server/backup/mysql.bak.$(date +%F).sql.gz
　　执行备份命令，  -A表示备份所有库，-B表示增加use 数据库名和drop等（导库时会直接覆盖原有的）。
　　导为了确保导库期间，数据库没有数据插入，可以再检查下主库状态信息。
　　mysql> show master status;
　　+------------------+----------+--------------+------------------+-------------------+
　　| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
　　+------------------+----------+--------------+------------------+-------------------+
　　| mysql-bin.000013 |      331 |              |                  |                   |
　　+------------------+----------+--------------+------------------+-------------------+
　　1 row in set (0.00 sec)
　　提示：无特殊情况，binlog文件及文件点事保持不变的。
　　mysql>
　　(7)导库完成后，解锁主库，恢复可写。
　　mysql> unlock table;
　　二、开始迁移数据
　　(1)把主库备份的mysql数据迁移到从库，通常可以使用scp，rsync
　　[root@master backup]# scp mysql.bak.2015-12-22.sql.gz root@192.168.11.35:/tmp
　　root@192.168.11.35's password:
　　mysql.bak.2015-12-22.sql.gz                      100%  164KB 163.9KB/s   00:00
　　[root@master backup]#
　　三、主库设置完毕，可以开始操作从库；
　　(1)设置server-id值并关闭binlog设置
　　[mysqld]
　　server_id = 34
　　#log_bin =/usr/local/data/mysql-bin
　　注意：上述两个参数必须放在my.cnf配置文件[mysqld]中，否则报错
　　server_id = 34可以是服务器IP后以为，避免重复。
　　修改配置文件后需要重启mysql
　　(2)检查配置文件
　　[root@master data]# grep -E "server-id|log-bin" /etc/my.cnf  检查配置结果
　　[root@slave tmp]# grep -E "server-id|log-bin" /etc/my.cnf
　　server-id = 35
　　#log-bin =/usr/local/data/mysql-bin
　　[root@slave tmp]#
　　(3)还原主库导出的数据库到从库
　　[root@slave tmp]# gzip -d mysql.bak.2015-12-22.sql.gz  解压
　　[root@slave tmp]# mysql -uroot -ptangbo  desc caiwu;
　　+-------+-------------+------+-----+---------+----------------+
　　| Field | Type        | Null | Key | Default | Extra          |
　　+-------+-------------+------+-----+---------+----------------+

　　|>　　| name  | varchar(60) | NO   |     | NULL    |                |
　　| ages  | varchar(60) | YES  |     | NULL    |                |
　　| job   | varchar(30) | YES  |     | NULL    |                |
　　| pay   | float       | YES  |     | NULL    |                |
　　| time  | date        | YES  |     | NULL    |                |
　　+-------+-------------+------+-----+---------+----------------+
　　6 rows in set (0.00 sec)
　　mysql>
　　(4)登陆从库配置同步参数：或不登陆数据库，在命令行快速执行change master的语句（适合在脚本中批量创建slave库用）
　　登陆数据库执行：
　　change master to    #连接master库
　　master_host='192.168.11.34',  #主库的IP
　　master_port=3306,             #这里是主库的端口，从库端口可以和主库不同
　　master_user='tangbo',         #主库上建立的用于复制的用户名
　　master_password='111111',     ##主库上建立的用于复制的密码
　　master_log_file='mysql-bin.000013', #show master status时看到的查看二进制日志文件名称，不能多出空格来
　　master_log_pos=331;                 #show master status时看到的二进制日志的偏移量，不能多出空格来
　　下面这个可以直接复制：
　　change master to
　　master_host='192.168.11.34',
　　master_port=3306,
　　master_user='tangbo',
　　master_password='111111',
　　master_log_file='mysql-bin.000013',
　　master_log_pos=331;
　　脚本下写法：
　　cat | mysql -uroot -ptangbo  show slave status\G
　　*************************** 1. row ***************************
　　Slave_IO_State: Waiting for master to send event
　　Master_Host: 192.168.11.34      #当前mysql master服务器主机
　　Master_User: tangbo
　　Master_Port: 3306
　　Connect_Retry: 60
　　Master_Log_File: mysql-bin.000013
　　Read_Master_Log_Pos: 634818
　　Relay_Log_File: slave-relay-bin.000002

　　>
　　>　　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: 634818

　　>　　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: 34
　　Master_UUID: 3a53cf38-9a9f-11e5-bdc9-52540055b912
　　Master_Info_File: /usr/local/data/master.info
　　SQL_Delay: 0
　　SQL_Remaining_Delay: NULL

　　Slave_SQL_Running_State: Slave has read all>　　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)
　　判断是否搭建成功就看IO和SQL两个线程是否显示为yes状态。
　　Slave_IO_Running: Yes  #负责从库去主库读取BINLOG日志，并写入从库的中继日志中
　　Slave_SQL_Running: Yes #负责读取并执行中继日志的binlog，转换SQL语句后应用到数据库汇总
　　提示：有关show master status 查看mysql手册
　　测试复制结果
　　五、相关mysql技术技巧概览
　　(1)配置忽略权限库同步参数
　　binlog-ignore-db=information_schema
　　binlog-ignore-db=mysql
　　(2)主从复制故障解决
　　show slave status;
　　有报错：Error xxxx desn't  exist
　　且 mysql> show slave status\G
　　*************************** 1. row ***************************
　　Slave_IO_Running: Yes
　　Slave_SQL_Running: No
　　Seconds_Behind_Master: NULL
　　解决办法：
　　stop slave;
　　set global sql_slave_skip_counter =1;   #=n表示忽略执行N个更新
　　start slave;
　　这样，Slave就会和Master去同步：主要看点：
　　mysql> show slave status\G
　　*************************** 1. row ***************************
　　Slave_IO_Running: Yes
　　Slave_SQL_Running: YES
　　Seconds_Behind_Master: 0 #0表示已经同步状态
　　(3)让mysql slave 记录binlog方法
　　在从库my.cnf中加入如下参数
　　log-slave-updates
　　log-bin=mysql13307-bin
　　expire logs days = 7
　　应用场景：级联复制或从库做数据备份
　　(4)严格设置从库只读
　　read-only的妙用，详情出查看文档
　　(5)生产环境确保从库只读？
　　1.mysql从服务器中加入 read-only参数
　　。   2.忽略mysql库已经information_schema库同步
　　3.授权从库用户时仅仅授权selesct权限。
　　(6)生产环境主库用户的授权；

　　grant select,insert,update,delete on bbpay.* to 'test'@'localhost'>　　(7)生产环境从库用户的授权

　　1.grant select on bbpay.* to 'test'@'localhost'>　　2.在my.cnf中添加read-only参数
　　六、生产环境主从库注意事项：
　　(1)第一次做从库如何做？
　　假如你的服务器只有主库，而且跑了应用了，现在由于业务需要第一个做从库，此时可能需要申请停机维护世界，；可以再
　　用户访问量最小，且不影响内部其他业务运转的时间点来停机配置主从复制，一般都在凌晨进行。
　　当然，也可以不申请，在定时任务备份时，每天夜里定时备份时做一些任务。
　　(2)不加班工作时间从容配置从库
　　当然了，也可以不申请，在定时任务备份时，每天的夜里服务器压力小时定时备份时做一些措施即可。
　　1.锁表备份全备一份
　　2.锁表前后取得show  master status值记录到日志里。
　　这样就可以再白天从容的实现主从同步了，这个脚本还是比较简单：如
　　[root@master tmp]# cat mysql_bak.sh
　　#!/bin/bash
　　##############################
　　#scripts by tangbo
　　#qq 79313760
　　##############################
　　mkdir -p /server/backup
　　MYUSER=root
　　MYPASS="tangbo"
　　MAIN_PATH=/server/backup
　　DATA_PATH=/server/backup
　　LOG_FILE=${DATA_PATH}/mysqllogs_`date +%F`.log
　　DATA_FILE=${DATA_PATH}/mysql_backup_`date +%F`.sql.gz
　　MYSQL_PATH=/usr/local/mysql/bin
　　MYSQL_CMD="$MYSQL_PATH/mysql -u$MYUSER -p$MYPASS"
　　MYSQL_DUMP="$MYSQL_PATH/mysqldump -u$MYUSER -p$MYPASS -A -B --flush-logs --single-transaction -e"
　　$MYSQL_CMD -e "flush tables with read lock;"
　　echo "----------show master status result----------" >> $LOG_FILE
　　$MYSQL_CMD -e "show master status;" >> $LOG_FILE
　　${MYSQL_DUMP} | gzip > $DATA_FILE
　　$MYSQL_CMD -e "unlock tables;"
　　mail -s "mysql slave log" 79313760@qq.com < $LOG_FILE
　　[root@master tmp]#
　　提示：脚本实际上就是把主从制作的过程自动化。
　　执行过程与结果：
　　[root@master tmp]# ls -l /server/backup/
　　total 336
　　-rw-r--r--. 1 root root 170596 Dec 22 21:32 mysql_backup_2015-12-22.sql.gz
　　-rw-r--r--. 1 root root 167799 Dec 22 00:52 mysql.bak.2015-12-22.sql.gz
　　-rw-r--r--. 1 root root    135 Dec 22 21:32 mysqllogs_2015-12-22.log
　　[root@master tmp]#
　　[root@master tmp]# cat /server/backup/mysqllogs_2015-12-22.log
　　----------show master status result----------
　　FilePositionBinlog_Do_DBBinlog_Ignore_DBExecuted_Gtid_Set
　　mysql-bin.000013634818
　　[root@master tmp]#
　　(3)有了主库备份文件和mysql-bin.000013内容做从库就很简单了。
　　下面开始不停主库一键批量创建从库；首先，把mysql_backup_2015-12-22.sql.gz发布到想做从库的机器上，也许N台。
　　开始制作批量做从库脚本，一键完成多台从库的制作：
　　[root@master tmp]# cat mysql_slave.sh
　　#!/bin/bash
　　##############################
　　#scripts by tangbo
　　#qq 79313760
　　##############################
　　MYUSER=root
　　MYPASS="tangbo"
　　MAIN_PATH=/server/backup
　　DATA_PATH=/server/backup
　　LOG_FILE=${DATA_PATH}/mysqllogs_`date +%F`.log
　　DATA_FILE=${DATA_PATH}/mysql_backup_`date +%F`.sql.gz
　　MYSQL_PATH=/usr/local/mysql/bin
　　MYSQL_CMD="$MYSQL_PATH/mysql -u$MYUSER -p$MYPASS"
　　#reconver
　　cd ${DATA_PATH}
　　gzip -d mysql_backup_`date +%F`.sql.gz
　　$MYSQL_CMD < mysql_backup_`date +%F`.sql.gz
　　#config slave
　　cat | $MYSQL_CMD > $LOG_FILE
　　$MYSQL_CMD -e "show slave status\G" |grep "IO_Running|SQL_Running" >> $LOG_FILE
　　mail -s "mysql slave log" 79313760@qq.com < $LOG_FILE
　　[root@master tmp]#
　　注意：上面脚本中change master 参数要根据mysqllogs_2015-12-22.log修改。
　　(4)主从库给开发人员读写分离用户的设置：
　　如：  主库(提供写服务): blog tangbo123 ip=192.168.111.1 port=3306
　　从库(仅仅提供读服务): blog tangbo123 ip=192.168.111.2 port=3306