什么是 Mtsql 主从？
MySQL 主从又叫做 Replication、AB 复制。简单讲就是 A 和 B 两台机器做主从后，在 A 上写数据，另外一台 B 也会跟着写数据，两者数据实时同步的
MySQL 主从是基于 binlog 的，主上须开启 binlog 才能进行主从。
主从过程大致有 3 个步骤
1）主将更改操作记录到 binlog 里
2）从将主的 binlog 事件(sql 语句)同步到从本机上并记录在 relaylog 里
3）从根据 relaylog 里面的 sql 语句按顺序执行
主上有一个 log dump 线程，用来和从的 I/O 线程传递 binlog
从上有两个线程，其中 I/O 线程用来同步主的 binlog 并生成 relaylog，另外一个 SQL 线程用来把 relaylog 里面的 sql 语句落地
使用场景：
01：数据需要备份的时候
02：除了数据备份外，还达到一个负载均衡的目的，减轻主库读的压力，但需要注意的是只有主才能做写操作，主和从都能做读操作
准备工作
主服务器和从服务器均安装好 Mysql 并启动起来（安装步骤请看：https://www.itwordsweb.com/?s=mysql）
这里使用 Mysql 5.6 版本
[root@am-01:~#] ps -aux | grep mysql
root 5176 0.0 0.1 11776 1108 ? S 3 月 22 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/am-01.pid
mysql 5314 0.1 32.7 1766876 328224 ? Sl 3 月 22 20:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/am-01.err --pid-file=/data/mysql/am-01.pid --socket=/tmp/mysql.sock
root 26534 0.0 0.0 112676 972 pts/1 S+ 23:06 0:00 grep --color=auto mysql
[root@am-01:~#] mysql -V
mysql Ver 14.14 Distrib 5.6.36, for linux-glibc2.5 (x86_64) using EditLine wrapper
#主服务器
[root@am-02:~#] ps -aux | grep mysql
root 17940 0.0 0.1 11776 1580 pts/0 S 00:09 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/am-02.pid
mysql 18047 0.1 44.8 1366572 449540 pts/0 Sl 00:09 0:05 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/am-02.err --pid-file=/data/mysql/am-02.pid
root 18204 0.0 0.0 112672 976 pts/0 S+ 01:02 0:00 grep --color=auto mysql
[root@am-02:~#] mysql -V
mysql Ver 14.14 Distrib 5.6.36, for linux-glibc2.5 (x86_64) using EditLine wrapper
#从服务器
配置主服务器
[root@am-01:~#] vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/tmp/mysql.sock
server-id=240
log_bin=am01
[root@am-01:~#] service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL....... SUCCESS!
[root@am-01:~#] cd /data/mysql/
[root@am-01:/data/mysql#] ls
am01.000001 am01.index auto.cnf ib_logfile0 mysql performance_schema zrlog
am-01.err am-01.pid ibdata1 ib_logfile1 mysql2 test
#修改主配置文件 my.cnf，server-id 和 log_bin，值可以随便取，记得即可
#重启 Mysql，可以见到/data/mysql/目录下生成了以 am01 开头的一些文件（这些文件很重要，是实现主从的根本）
#binlog 很重要，会记录着你对数据库的操作，其实你可以直接使用 binlog 来做数据库回复，前提是 binlog 记录的操作步骤是完整的
[root@am-01:/data/mysql#] mysqldump -uroot -pitsupport.0 zrlog > /tmp/zrlog.sql
Warning: Using a password on the command line interface can be insecure.
[root@am-01:/data/mysql#] mysql -uroot -pitsupport.0 -e "create database am"
Warning: Using a password on the command line interface can be insecure.
[root@am-01:/data/mysql#] mysql -uroot -pitsupport.0 am < /tmp/zrlog.sql
Warning: Using a password on the command line interface can be insecure.
[root@am-01:/data/mysql#] mysqldump -uroot -pitsupport.0 mysql2 > /tmp/mysql2.sql
Warning: Using a password on the command line interface can be insecure.
[root@am-01:/data/mysql#] ls /tmp/*.sql
tmp/mysql2.sql /tmp/test.sql /tmp/zrlog.sql
#把之前实验留下的 zrlog 库备份一下，并创建新库 am，再把备份的 zrlog.sql 恢复到新库 am 中
#同时也把 mysql2 和 test 的库也备份一下，之后拿来做实验
[root@am-01:/data/mysql#] mysql -uroot -pitsupport.0
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 354
Server version: 5.6.36-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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 'am'@'172.17.1.242' identified by 'itsupport.0';
Query OK, 0 rows affected (0.04 sec)
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+-------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| am01.000001 | 10267 | | | |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> quit
Bye
#创建一个新的用户，用来做主从的用户
#"flush tables with read lock;"是把数据库锁定，目的是为了不让其继续写，因为之后需要用从服务器备份，需要保持一致，这样的才能实现同步
#"show master status;"，把 File 和 Position 的值记着，同时 File 的路径也需要记着，后面要用
配置从服务器
[root@am-02:~#] vim /etc/my.cnf
basedir = /usr/local/mysql
datadir = /data/mysql
server_id = 242
socket = /tmp/mysql.sock
[root@am-02:~#] service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL..... SUCCESS!
#从服务器增加 server-id，要和主服务器区分开来，从服务器不需要设置 log_bin，重启一下 Mysql
[root@am-02:/tmp#] scp 172.17.1.240:/tmp/*.sql /tmp/
root@172.17.1.240's password:
mysql2.sql 100% 642KB 642.1KB/s 00:00
zrlog.sql 100% 9872 9.6KB/s 00:00
#把主服务器备份的 sql 文件拉取到从服务器上
[root@am-02:~#] mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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 mysql2;
Query OK, 1 row affected (0.00 sec)
mysql> create database zrlog;
Query OK, 1 row affected (0.00 sec)
mysql> create database am;
Query OK, 1 row affected (0.00 sec)
mysql> quit
Bye
#从服务器也创建跟主服务器一样的库
[root@am-02:/tmp#] mysql -uroot am < /tmp/zrlog.sql
[root@am-02:/tmp#] mysql -uroot zrlog < /tmp/zrlog.sql
[root@am-02:/tmp#] mysql -uroot mysql2 < /tmp/mysql2.sql
#把从主服务器拷贝过来的 sql 文件恢复到对应数据库上，切记，从服务器和主服务器的数据要保持一致
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> change master to master_host='172.17.1.240', master_user='am', master_password='itsupport.0', master_log_file='am01.000001', master_log_pos=10267;
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.1.240
Master_User: am
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: am01.000001
Read_Master_Log_Pos: 10267
Relay_Log_File: am-02-relay-bin.000002
Relay_Log_Pos: 278
Relay_Master_Log_File: am01.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: 10267
Relay_Log_Space: 451
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: 240
Master_UUID: c0a7f5a5-2606-11e8-957d-000c2908ac45
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 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)
[root@am-01:/tmp#] mysql -uroot -pitsupport.0
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 7042
Server version: 5.6.36-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
#"master_host"指定主服务器地址，"master_user"指定做主从的用户，"master_password"指定做主从的用户的密码，"master_llog_file"为主服务器的 File 列，master_log_pos 为主服务器的 Position 列
#切记要先确保主和从的数据一致，然后先"stop slave"，最后"start slave;"
#最后，主服务器要把数据库解锁，那样才能实现同步
知识点：
01：使用"show slave stauts\G"看是否有
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
有的话一般主从已经设置成功
02：关注下面这些参数的值，用来判断主从的状态
Seconds_Behind_Master: 0 //为主从延迟的时间
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
配置参数介绍：
主服务器上
binlog-do-db= //同步指定的库，多个库用逗号分割
binlog-ignore-db= //忽略指定库
从服务器上
replicate_do_db=
replicate_ignore_db=
replicate_do_table=
replicate_ignore_table=
#一般不用以上四个参数
replicate_wild_do_table= //如 am.%, 支持通配符%
replicate_wild_ignore_table=
测试主从是否成功
[root@am-01:~#] mysql -uroot -pitsupport.0
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 7159
Server version: 5.6.36-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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> use am;
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_am |
+--------------+
| comment |
| link |
| log |
| lognav |
| plugin |
| tag |
| type |
| user |
| website |
+--------------+
9 rows in set (0.00 sec)
mysql> drop table am.tag;
Query OK, 0 rows affected (0.10 sec)
mysql> show tables;
+--------------+
| Tables_in_am |
+--------------+
| comment |
| link |
| log |
| lognav |
| plugin |
| type |
| user |
| website |
+--------------+
8 rows in set (0.00 sec)
#在主服务器上把 am 库的 tag 表删除
[root@am-02:~#] mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.6.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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> use am;
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_am |
+--------------+
| comment |
| link |
| log |
| lognav |
| plugin |
| type |
| user |
| website |
+--------------+
8 rows in set (0.00 sec)
#在从服务器上检查，可以发现，从服务器上的 tag 表也被删除了
知识点：
假如发生主从不一致，即"Slave_SQL_Running: No"的时候，可以先"stop slave"，最后"start slave;"，看能否修复，如果无法修复，就确保两边数据一致，怎么确保一致？从服务器重新从主服务器拉取 sql 备份，然后做恢复，简单来讲，就是重新来，如果数据确保一致了，从服务器就可以直接从"change master"开始
切记，主服务器的 Position 列的值很敏感，会经常变动，如果重新做主从，要先把主服务器锁表（也可以不锁表，后面会讲到），并记录好这个值
扩展
mysql 主从配置 uuid 相同错误解决：
https://www.2cto.com/database/201412/364479.html
Comments | NOTHING