MySQL主从复制实战
一、复制准备(1)定义机器角色：
主库 192.168.11.34 port 3306
# cat /etc/sysconfig/network
HOSTNAME=master
从库 192.168.11.35 port 3306
#cat /etc/sysconfig/network
HOSTNAME=slave
提示：一般做主从，主从服务器在不同的机器上，监听端口为3306。可以根据实际情况进行修改。
(2)主库上执行操作
设置server-id值并开启binlog设置
根据mysql的同步原理，我们知道复制的关键因素就是binlog日志。
执行# vim /etc/my.cnf，编辑my.cnf配置文件，添加两个参数
server_id = 34
log_bin =/usr/local/data/mysql-bin
注意：上述两个参数必须放在my.cnf配置文件中，否则报错。
server_id = 34可以是服务器IP后以为，避免重复。
修改配置文件后需要重启mysql。
# 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），且可以停机，直接打包数据迁移。
# mkdir /server/backup/ -p创建备份目录
# 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
# 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
#
三、主库设置完毕，可以开始操作从库；
(1)设置server-id值并关闭binlog设置
server_id = 34
#log_bin =/usr/local/data/mysql-bin
注意：上述两个参数必须放在my.cnf配置文件中，否则报错
server_id = 34可以是服务器IP后以为，避免重复。
修改配置文件后需要重启mysql
(2)检查配置文件
# grep -E "server-id|log-bin" /etc/my.cnf检查配置结果
# grep -E "server-id|log-bin" /etc/my.cnf
server-id = 35
#log-bin =/usr/local/data/mysql-bin
#
(3)还原主库导出的数据库到从库
# gzip -d mysql.bak.2015-12-22.sql.gz解压
# mysql -uroot -ptangbodesc 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 -ptangboshow 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'texist
且 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.锁表前后取得showmaster status值记录到日志里。
这样就可以再白天从容的实现主从同步了，这个脚本还是比较简单：如
# 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
#
提示：脚本实际上就是把主从制作的过程自动化。
执行过程与结果：
# 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
#
# cat /server/backup/mysqllogs_2015-12-22.log
----------show master status result----------
FilePositionBinlog_Do_DBBinlog_Ignore_DBExecuted_Gtid_Set
mysql-bin.000013634818
#
(3)有了主库备份文件和mysql-bin.000013内容做从库就很简单了。
下面开始不停主库一键批量创建从库；首先，把mysql_backup_2015-12-22.sql.gz发布到想做从库的机器上，也许N台。
开始制作批量做从库脚本，一键完成多台从库的制作：
# 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
#
注意：上面脚本中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
页:
[1]