mysql主从复制实践

　　

一、在主库master上执行操作配置：
1.设置server-id值并开启binlog功能参数。
[root@oldboyedu-01 ~]# egrep "server-id|log-bin" /data/3306/my.cnf
#log-bin = /data/3306/mysql-bin
log-bin = /data/3306/mysql3306-bin
server-id = 1
重启主库mysql服务：
[root@oldboyedu-01 ~]# /data/3306/mysql restart
登录主库，检查参数的更改情况：
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |

2.建立用于主从复制的账号。
mysql> grant replication slave on *.* to 'rep'@'10.0.0.%' identified by 'oldboy123';
mysql> flush privileges;
mysql> select user,host from mysql.user where user='rep';
+------+----------+
| user | host |
+------+----------+
| rep | 10.0.0.% |

3.实现对主库数据库锁表只读。
mysql> flush table with read lock;
mysql> show variables like '%timeout%';
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| interactive_timeout | 28800 |
| wait_timeout | 28800 |
+----------------------------+----------+
锁表后查看主库状态，查看当前binlog日志文件名和二进制binlog日志偏移量。
mysql> show master status;
+----------------------+----------+--------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+---------------------------------------------+
| mysql3306-bin.000010 | 333 | | mysql,performance_schema,information_schema |

锁表后，重开窗口导出数据库所有数据进行数据迁移。
[root@oldboyedu-01 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock --events -A -B |gzip >/opt/mysql_bak_new_$(date +%F).sql.gz
[root@oldboyedu-01 ~]# ll /opt/mysql_bak_new_2018-03-03.sql.gz
-rw-r--r-- 1 root root 144667 Mar 3 15:56 /opt/mysql_bak_new_2018-03-03.sql.gz
检查主库状态信息：
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show master status;"
+----------------------+----------+--------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+---------------------------------------------+
| mysql3306-bin.000010 | 333 | | mysql,performance_schema,information_schema |
导出数据完成后解锁主库：
mysql> unlock tables;

4.将主库导出的mysql数据库数据迁移到从库，即全量备份迁移。

二、在从库slave上执行操作配置：
1.设置server-id值并关闭binlog功能参数。
[root@oldboyedu-01 ~]# egrep "server-id|log-bin" /data/3307/my.cnf
#log-bin = /data/3307/mysql-bin
server-id = 3

[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 3 |

2.将主库mysqldump导出的数据解压，恢复到从库。
[root@oldboyedu-01 ~]# gzip -d /opt/mysql_bak_new_2018-03-03.sql.gz
[root@oldboyedu-01 ~]# ll /opt/mysql_bak_new_2018-03-03.sql
-rw-r--r-- 1 root root 530910 Mar 3 15:56 /opt/mysql_bak_new_2018-03-03.sql

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock </opt/mysql_bak_new_2018-03-03.sql
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| d123 |
| mysql |
| oldboy |
| oldgirl |
| test |
+--------------------+

3.登录从库，配置复制参数。
CHANGE MASTER TO
MASTER_HOST='10.0.0.200',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='oldboy123',
MASTER_LOG_FILE='mysql3306-bin.000010',
MASTER_LOG_POS=333;
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.200',
-> MASTER_PORT=3306,
-> MASTER_USER='rep',
-> MASTER_PASSWORD='oldboy123',
-> MASTER_LOG_FILE='mysql3306-bin.000010',
-> MASTER_LOG_POS=333;
[root@oldboyedu-01 ~]# cat /data/3307/data/master.info
18
mysql3306-bin.000010 //
333 //
10.0.0.200
rep
oldboy123
3306
60
0

4.启动从库同步开关，测试主从复制情况。
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "start slave;"
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "show slave status\G;"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.200
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql3306-bin.000010
Read_Master_Log_Pos: 333
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 257
Relay_Master_Log_File: mysql3306-bin.000010
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: 333
Relay_Log_Space: 407
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: 1

测试主从复制结果：
主库新写入数据，例建库wenkai：
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "create database wenkai;"
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show databases like 'wenkai';"
+-------------------+
| Database (wenkai) |
+-------------------+
| wenkai |
观察从库数据情况，可知主从库是同步的。
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "show databases like 'wenkai';"
+-------------------+
| Database (wenkai) |
+-------------------+
| wenkai |

[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show master status"
+----------------------+----------+--------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+---------------------------------------------+
| mysql3306-bin.000010 | 420 | | mysql,performance_schema,information_schema |
+----------------------+----------+--------------+---------------------------------------------+
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "show slave status\G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.200
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql3306-bin.000010
Read_Master_Log_Pos: 420 //
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 344
Relay_Master_Log_File: mysql3306-bin.000010
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: 420
Relay_Log_Space: 494
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: 1
[root@oldboyedu-01 ~]# cat /data/3307/data/master.info
18
mysql3306-bin.000010 //
420 //
10.0.0.200
rep
oldboy123
3306
60
0