MySQL主从介绍
MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步的
MySQL主从是基于binlog的，主上须开启binlog才能进行主从
主从过程大致有3个步骤
- 主将更改操作记录到binlog里
- 从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里
- 从根据relaylog里面的sql语句按顺序执行
主上有一个log dump线程，用来和从的I/O线程传递binlog
从上有两个线程，其中I/O线程用来同步主的binlog并生成relaylog(中继日志)，另外一个SQL线程用来把relaylog里面的sql语句落地
原理图
配置 - 主
在配置文件中mysqld模块下添加两行(注意值中最好不要有特殊符号，比如横线，可能会导致启动失败)
server-id = 134master
log_bin = syntest
[root@test-a ~]# vim /etc/my.cnf
[root@test-a ~]# cat /etc/my.cnf
# [mysql]
[client]
socket = /usr/local/mysql/mysql.sock
# The MySQL server
[mysqld]
port = 3306
socket = /usr/local/mysql/mysql.sock
server-id = 134master
log_bin = syntest
#skip-grant-tables
datadir=/data/mysql
log-error=/usr/local/mysql/db.err
pid-file=/usr/local/mysql/mysqld.pid
character-set-server = utf8
[mysql.server]
basedir=/usr/local/mysql
[root@test-a ~]# /etc/init.d/mysqld restart # 启动
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@test-a ~]# ls -lh /data/mysql # 可以看到有新生成以syntest开头的文件
total 185M
-rw-r-----. 1 mysql mysql 56 Nov 25 15:29 auto.cnf
drwxr-x---. 2 mysql mysql 19 Dec 6 15:25 db1
-rw-r-----. 1 mysql mysql 351 Dec 17 14:28 ib_buffer_pool
-rw-r-----. 1 mysql mysql 76M Dec 17 14:28 ibdata1
-rw-r-----. 1 mysql mysql 48M Dec 17 14:28 ib_logfile0
-rw-r-----. 1 mysql mysql 48M Nov 25 15:29 ib_logfile1
-rw-r-----. 1 mysql mysql 12M Dec 17 14:28 ibtmp1
drwxr-x---. 2 mysql mysql 4.0K Nov 25 15:29 mysql
drwxr-x---. 2 mysql mysql 4.0K Dec 7 15:57 mysql2
drwxr-x---. 2 mysql mysql 8.0K Nov 25 15:29 performance_schema
-rw-r-----. 1 mysql mysql 154 Dec 17 14:28 syntest.000001
-rw-r-----. 1 mysql mysql 17 Dec 17 14:28 syntest.index
drwxr-x---. 2 mysql mysql 8.0K Nov 25 15:29 sys
drwxr-x---. 2 mysql mysql 57 Dec 7 08:28 test_db0
drwxr-x---. 2 mysql mysql 4.0K Dec 14 09:15 zrlog
[root@test-a ~]# mysqldump -uroot -p zrlog > /bak/zrlog.sql
Enter password:
[root@test-a ~]# ls /bak/
zrlog.sql
[root@test-a ~]# mysql -uroot -p -e "create database syn_zrlog"
Enter password:
[root@test-a ~]# mysql -uroot -p syn_zrlog < /bak/zrlog.sql
Enter password:
[root@test-a ~]# ls /data/mysql/
auto.cnf ibdata1 ibtmp1 performance_schema syn_zrlog zrlog
db1 ib_logfile0 mysql syntest.000001 sys
ib_buffer_pool ib_logfile1 mysql2 syntest.index test_db0
[root@test-a ~]# ls /data/mysql/ -lh
total 185M
-rw-r-----. 1 mysql mysql 56 Nov 25 15:29 auto.cnf
drwxr-x---. 2 mysql mysql 19 Dec 6 15:25 db1
-rw-r-----. 1 mysql mysql 351 Dec 17 14:28 ib_buffer_pool
-rw-r-----. 1 mysql mysql 76M Dec 17 14:42 ibdata1
-rw-r-----. 1 mysql mysql 48M Dec 17 14:42 ib_logfile0
-rw-r-----. 1 mysql mysql 48M Nov 25 15:29 ib_logfile1
-rw-r-----. 1 mysql mysql 12M Dec 17 14:40 ibtmp1
drwxr-x---. 2 mysql mysql 4.0K Nov 25 15:29 mysql
drwxr-x---. 2 mysql mysql 4.0K Dec 7 15:57 mysql2
drwxr-x---. 2 mysql mysql 8.0K Nov 25 15:29 performance_schema
-rw-r-----. 1 mysql mysql 14K Dec 17 14:42 syntest.000001
-rw-r-----. 1 mysql mysql 17 Dec 17 14:28 syntest.index
drwxr-x---. 2 mysql mysql 4.0K Dec 17 14:42 syn_zrlog
drwxr-x---. 2 mysql mysql 8.0K Nov 25 15:29 sys
drwxr-x---. 2 mysql mysql 57 Dec 7 08:28 test_db0
drwxr-x---. 2 mysql mysql 4.0K Dec 14 09:15 zrlog
[root@test-a ~]# grant replication slave on *.* to 'repl'@'192.168.77.129' identified by 'test111'
-bash: grant: command not found
[root@test-a ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.23-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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> grant replication slave on *.* to 'repl'@'192.168.77.129' identified by 'test111'
-> ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show grants for [email protected]
-> ;
+-----------------------------------------------------------+
| Grants for [email protected] |
+-----------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.77.129' |
+-----------------------------------------------------------+
1 row in set (0.00 sec)
mysql> flush tables with read lock; # 锁表，不让再继续读写，保证数据一致
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| syntest.000001 | 13691 | | | |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@test-a ~]# mysqldump -uroot -p test_db0 > /bak/test_db0.sql
Enter password:
[root@test-a ~]# mysqldump -uroot -p db1 > /bak/db1.sql
Enter password:
配置 - 从
编辑my.cnf，配置server-id=129slave，要求和主不一样
[root@centos0 ~]# vim /etc/my.cnf
[root@centos0 ~]# cat /etc/my.cnf
# [mysql]
[client]
socket = /usr/local/mysql/mysql.sock
# The MySQL server
[mysqld]
port = 3306
socket = /usr/local/mysql/mysql.sock
server-id = 129
#skip-grant-tables
datadir=/data/mysql
log-error=/usr/local/mysql/db.err
pid-file=/usr/local/mysql/mysqld.pid
character-set-server = utf8
[mysql.server]
basedir=/usr/local/mysql
[root@centos0 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS
[root@centos0 ~]# scp 192.168.77.134:/bak/*.sql /bak/
The authenticity of host '192.168.77.134 (192.168.77.134)' can't be established.
ECDSA key fingerprint is ad:9c:a3:94:7d:9b:e6:be:02:67:d0:ab:0c:de:2e:f3.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.77.134' (ECDSA) to the list of known hosts.
[email protected]'s password:
db1.sql 100% 1258 1.2KB/s 00:00
test_db0.sql 100% 1787 1.8KB/s 00:00
zrlog.sql 100% 9935 9.7KB/s 00:00
[root@centos0 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.23
Copyright (c) 2000, 2018, 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> create database db1;
Query OK, 1 row affected (0.05 sec)
mysql> create database test_db0;
Query OK, 1 row affected (0.05 sec)
mysql> create database zrlog;
Query OK, 1 row affected (0.04 sec)
mysql> quit
Bye
[root@centos0 ~]# mysql -uroot db1 < /bak/db1.sql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@centos0 ~]# ^[[Cm^Cql -uroot db1 < /bak/db1.sql
[root@centos0 ~]#
[root@centos0 ~]#
[root@centos0 ~]# mysql -uroot -p db1 < /bak/db1.sql
Enter password:
[root@centos0 ~]# mysql -uroot -p test_db0 < /bak/test_db0.sql
Enter password:
[root@centos0 ~]# mysql -uroot -p zrlog < /bak/zrlog.sql
Enter password:
[root@centos0 ~]# ls /data/mysql/
auto.cnf ib_buffer_pool ib_logfile0 ibtmp1 performance_schema test_db0
db1 ibdata1 ib_logfile1 mysql sys zrlog
[root@centos0 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.23 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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> stop slave;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> change master to master_host='192.168.77.134', master_user='repl', master_password='test111', master_log_file='syntest.000001', master_log_pos=13691;
Query OK, 0 rows affected, 2 warnings (0.20 sec)
mysql> start slave;
Query OK, 0 rows affected (0.24 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.77.134
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: syntest.000001
Read_Master_Log_Pos: 13691
Relay_Log_File: centos0-relay-bin.000002
Relay_Log_Pos: 318
Relay_Master_Log_File: syntest.000001
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: 13691
Relay_Log_Space: 527
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: 140509184
Master_UUID: e43279dd-f083-11e8-b63a-000c29b95699
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.02 sec)
从配置完成后通过show slave status看Slave_IO_Running和Slave_SQL_Running是Yes则说明同步配置成功，这时需要去主库服务上打开表锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
Seconds_Behind_Master: 0 # 主从延迟的时间
测试主从同步
主库上:
mysql> use test_db0;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+--------------------+
| Tables_in_test_db0 |
+--------------------+
| test_tb1 |
+--------------------+
1 row in set (0.00 sec)
mysql> drop table test_tb1;
Query OK, 0 rows affected (0.33 sec)
从库上查看:
mysql> show tables;
Empty set (0.00 sec)
同步OK.