MySQL主从复制原理：
1、MySQL的主从复制是一个异步的复制过程（虽然一般情况下感觉是实时的），数据将从一个MySQL数据库（我们称之为Master）复制到另一个MySQL数据库（我们称之为Slave），在Master与Salve之间实现整个主从复制的过程是由三个线程参与完成的。其中有两个线程（SQL线程和IO线程）在Slave端，另外一个线程（I/O线程）在Master端。
2、要实现MySQL的主从复制，首先必须打开Master端的binlog记录功能，否则就无法实现。因为整个复制过程实际上就是Slave从Master端获取binlog日志，然后再在Slave上以相同顺序执行获取的binlog日志中所记录的各种SQL操作。注意binlog日志只记录增删改（不记录查询语句）
3、从库IO在第一次把信息放入到relay中后会触发SQL线程，然后后边就交给SQL线程来处理，把binlog文件中的SQL语句解析出来后变成SQL语句放入数据库中 MySQL主从复制原理图：
MySQL主从复制小结
1）主从复制是异步的逻辑的SQL语句级的复制。
2）复制时，主库有一个I/O线程，从库有两个线程，I/O和SQL线程
3）实现主从复制的必要条件是主库要开启记录binlog功能
4）作为复制的所有MySQL节点的server-id都不能相同(0<server-id<2^23-1)
5）binlog文件只记录对数据库有更改的SQL语句（来自主数据库内容的变更，不记录任何查询（select，show）语句
主从复制条件
1）主库开启binlog功能（从库除非做双向复制）
2）主库建立同步账号
3）从库配置master.info（CHANGE MASTER TO …来实现）
4）复制开关：start slave
需要了解一下概念
1）3线程，主库（IO），从库IO和SQL，及作用
2）master.info作用
3）relay-log作用
4）异步复制和同步复制的区别
5）binlog作用
环境
系统：redhat6.5
防火墙：保持关闭
selinux=disabled
master机：server4 172.25.29.4/24
slave机： server5 172.25.29.5/24
一、master 机和 slave 机安装 mysql
开启mysql并安全初始化
[root@server4 ~]# /etc/init.d/mysqld start
Initializing MySQL database: [ OK ]
Installing validate password plugin: [ OK ]
Starting mysqld: [ OK ]
[root@server4 ~]# grep password /var/log/mysqld.log
2018-07-05T02:15:15.875161Z 1 [Note] A temporary password is generated for root@localhost: f>F8=!hOlxlg
2018-07-05T02:15:25.526824Z 0 [Note] Execution of init_file '/var/lib/mysql/install-validate-password-plugin.DDmniu.sql' started.
2018-07-05T02:15:25.553545Z 0 [Note] Execution of init_file '/var/lib/mysql/install-validate-password-plugin.DDmniu.sql' ended.
2018-07-05T02:15:27.398597Z 0 [Note] Shutting down plugin 'sha256_password'
2018-07-05T02:15:27.398601Z 0 [Note] Shutting down plugin 'mysql_native_password'
2018-07-05T02:15:29.140899Z 3 [Note] Access denied for user 'UNKNOWN_MYSQL_USER'@'localhost' (using password: NO)
[root@server4 ~]#[root@server4 ~]# mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root:
The existing password for the user account root has expired. Please set a new password.
New password:
Re-enter new password:
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.
Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) :
... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done!
[root@server4 ~]# 二、配置主从复制（常用）
mysql主库（master）
（1）配置主配置文件（/etc/my.cnf）
[root@server4 ~]# vim /etc/my.cnf
[root@server4 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@server4 ~]#配置数据库
在主库上建立帐户并授权
mysql> grant replication slave on *.* to wsg@'172.25.29.%' identified by 'SHUAIdiea1999.';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
mysql>查看二进制日志是否打开
mysql> show variables like 'log_%';
+----------------------------------------+--------------------------------+
| Variable_name | Value |
+----------------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_builtin_as_identified_by_password | OFF |
| log_error | /var/log/mysqld.log |
| log_error_verbosity | 3 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_syslog | OFF |
| log_syslog_facility | daemon |
| log_syslog_include_pid | ON |
| log_syslog_tag | |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |
| log_warnings | 2 |
+----------------------------------------+--------------------------------+
21 rows in set (0.01 sec)
mysql> 查看主库的状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 598 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> mysq从库（slave）
配置文件（/etc/my.cnf）
[root@server5 ~]# vim /etc/my.cnf
[root@server5 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@server5 ~]# 配置数据库
mysql> change master to master_host='172.25.29.4',master_user='wsg',master_password='SHUAIdiea1999.',master_log_file='mysql-bin.000001',master_log_pos=598;
Query OK, 0 rows affected, 2 warnings (0.25 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.29.4
Master_User: wsg
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 598
Relay_Log_File: server5-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes当看到Slave_IO_Running: Yes以及Slave_SQL_Running: Yes，则表示slave库已经正常运行了
当出现Slave_IO_Running: Connecting的提示时，说明主库和从库没有连接上，有以下三点原因：
1.网络问题：检查网络连接是否能够连接上
2.密码或POS号错误：查看pos号和主库的号是否对应
3.防火墙的问题：查看主库防火墙的策略，数据库是否拒绝外来连接，然后做相应的改动
主从测试:
主库创建数据库：
mysql> create database user;
Query OK, 1 row affected (0.03 sec)
mysql> use user;
Database changed
mysql> create table usertable (
-> id varchar(10) not null,
-> username varchar(20) not null);
Query OK, 0 rows affected (0.12 sec)
mysql> insert into usertable values('123','hwj');
Query OK, 1 row affected (0.01 sec)
mysql> insert into usertable values('456','zh');
Query OK, 1 row affected (0.03 sec)
mysql> select * from usertable;
+-----+----------+
| id | username |
+-----+----------+
| 123 | hwj |
| 456 | zh |
+-----+----------+
2 rows in set (0.00 sec)
mysql> 从库进行查看：
mysql> select * from user.usertable;
+-----+----------+
| id | username |
+-----+----------+
| 123 | hwj |
| 456 | zh |
+-----+----------+
2 rows in set (0.00 sec)
mysql> 利用mysql5.7的GTID来实现主从复制
GTID
GTID(Global Transaction ID)是对于一个已提交事务的编号，并且是一个全局唯一的编号。它的官方定义如下：
GTID = source_id ：transaction_id
每一个 GTID 代表一个数据库事务
配置文件（/etc/my.cnf）并重启mysql（主从都配置一样）
配置从库：
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to master_host='172.25.29.4',master_user='wsg',master_password='SHUAIdiea1999.',MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.08 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.29.4
Master_User: wsg
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: server5-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-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: 154
Relay_Log_Space: 576
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
Master_UUID: 41934da7-7ff9-11e8-92a8-525400a978b5
Master_Info_File: /var/lib/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: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> 测试：
主库继续往表格里边插入字段
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| user |
+--------------------+
5 rows in set (0.00 sec)
mysql> use user;
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> select * from usertable;
+-----+----------+
| id | username |
+-----+----------+
| 123 | hwj |
| 456 | zh |
+-----+----------+
2 rows in set (0.00 sec)
mysql> insert into usertable values ('789','wsg');
Query OK, 1 row affected (0.03 sec)
mysql> select * from usertable;
+-----+----------+
| id | username |
+-----+----------+
| 123 | hwj |
| 456 | zh |
| 789 | wsg |
+-----+----------+
3 rows in set (0.00 sec)
mysql> 从库进行查看：
143
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
