MySQL主从复制
1、复制概述
Mysql内建的复制功能是构建大型，高性能应用程序的基础。将Mysql的数据分布到多个系统上去，这种分布的机制，是通过将Mysql的某一台主机的数据复制到其它主机（slaves）上，并重新执行一遍来实现的。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。
请注意当你进行复制时，所有对复制中的表的更新必须在主服务器上进行。否则，你必须要小心，以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。
1.1 MySQL支持的复制类型
-
基于语句的复制： 在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时， 会自动选着基于行的复制。
-
基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持
-
混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制
1.2 主从复制的作用
- 实现数据的高可用性和容错性
- 备份数据
- 负载均衡
- 数据分布
1.3 主从复制的工作流程
当有数据来写入到master主机时，数据发生改变，master会将修改写入到bin_log日志中，同时主服务器与从服务器之间会生成一个IO，从服务器会读到bin_log中的数据，会将其中的修改sql语句在执行一次，从而达到主服务器数据发生改变时，同步到从服务器完成主从复制。
1.4 环境搭建
- mysql 5.0 及以上
- 一台centos主机 --主服务器
- 一台centos主机 --从服务器
1.5 主服务器配置
主服务器配置相对来说，比较简单，首先打开MySQL配置文件，linux在 /etc/my.cnf下，windows默认在C:\Program Files\MySQL\MySQL Server 5.7\my-default.ini中。此处以linux为例；
vi /etc/my.cnf #进入MySQL的配置文件，输入i，进入插入模式
在[mysqld]下加入以下未注释内容
最后按下esc键，输入 :wq 保存退出，windows同理，只是配置文件名不同。
然后添加完之后，登录自己的服务器，给从服务器授权。
mysql> grant replication slave on *.* to '用户名'@'主服务器的ip' identified by '你的主服务器密码';
# 记得所有授权的操作都必须重新加载权限配置,mysql命令以 ; 结尾。
mysql> flush privileges;
但是对于一些刚开始使用MySQL的人来说，会出现以下问题；
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
他会告诉你，你的密码不符合当前策略，所以你需要对你的密码策略进行修改,当然，修改之前需要先查看一下，以下代码帮助你完成策略的查看。
mysql> select @@validate_password_policy;
+----------------------------+
| @@validate_password_policy |
+----------------------------+
| MEDIUM |
+----------------------------+
1 row in set (0.00 sec)
mysql> show variables like 'validate_password%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 0 |
| validate_password_number_count | 1 |
| validate_password_policy | LOW |
| validate_password_special_char_count | 1 |
+--------------------------------------+-------+
7 rows in set (0.04 sec)
此处可以得出，你的密码需要满足8位长度，需要支持大小写、数字、特殊字符。
本机修改如下：
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_mixed_case_count=0;
Query OK, 0 rows affected (0.01 sec)
mysql> set global validate_password_number_count = 3;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_number_count = 3;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_special_char_count = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length = 6;
Query OK, 0 rows affected (0.00 sec)
#重新设置你的密码
mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'your password';
Query OK, 0 rows affected (0.01 sec)
#重新加载权限配置，要把这条语句当做标准流程来写，多写几次没关系，少写一次可能就会很难受...
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
# 重新授权
mysql> grant all privileges on *.* to root@' %' identified by 'your password' with grant option;
Query OK, 0 rows affected, 1 warning (0.01 sec)
#重新加载权限配置
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
然后基本密码就完成了。现在检查一下你的master状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 1000 | yourtables | | |
+------------------+----------+--------------+------------------+-------------------+
1 row login set (0.00 sec)
此处在对这些名词做一下解释：
- file 就是你所生成的二进制文件名称
- position 是你的文件所处的位置，但是在mysql中它应该是偏移量
- binlog_do_db 就是你响应进行主从复制的数据库
其他的暂时不用了解。
重启mysql服务。进行从服务器配置；
1.6 从服务器配置
从服务器配置与主服务器配置类似，同样需要注意的是serverid必须保证唯一。输入vi /etc/my.cnf进入编辑器，输入以下内容。
log_bin= mysql-bin
server_id = 2
relay_log= mysql-relay-bin
log_slave_updates = 1
read_only= 1
我们之前通过以下命令查看了master主配置的信息，此时我们只需要将我们所知道的信息再告诉从服务器，让他们之间可以通讯。
登录mysql服务器，命令为： mysql -uroot -p 输入密码，然后输入
change master to master_host='主服务器ip',master_user='主服务器名（一般为
root）',master_password='主服务器密码',master_log_file='之前查看master主机状态时所对应
的文件名--mysql-bin.000001',master_log_pos='偏移量';
#以本机为例
change master to
master_host='10.7.16.217',
master_user='root',
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=1000;
此处如果还是出现密码问题，只需要将主服务器上关于密码的配置在copy过来到从服务器上即可。
然后开启slave
mysql> start slave; #开启slave,同样的关闭就是 stop slave。
#输入以下命令，查看slave状态,不输入\G会出现繁星点点的样子，不信你可以试一下
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.7.16.217
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 1000
Relay_Log_File: localhost-relay-bin.000003
Relay_Log_Pos: 1166
Relay_Master_Log_File: mysql-bin.000012
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: 1000
Relay_Log_Space: 1377
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: e4ea9a95-3356-11ea-bdf8-080027922971
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: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
此处主要注意两行即可：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果都为yes，表示配置成功，如果出现connecting或者no的情况，上述权限配置基本就可以解决，如果不能解决，则可能是端口未开放，需要在主服务器上开放端口，默认为3306；
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --relaod
接下来就是测试了
主库中插入一条命令；
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 3 | xlj |
+----+------+
1 row in set (0.00 sec)
mysql> insert into test(id,name) values(default,'123');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 3 | xlj |
| 4 | 123 |
+----+------+
2 rows in set (0.00 sec)
在从库中同样查询一下
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 3 | xlj |
+----+------+
1 row in set (0.00 sec)
mysql> mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 3 | xlj |
| 4 | 123 |
+----+------+
2 rows in set (0.00 sec)
大功告成！！！
如有问题，欢迎讨论，大家一起学习！
qq:1144249977
1961
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
