MySQL主从复制【Linux】
一、环境准备
系统版本：centos 7
1、服务器：
server1:182.92.209.212 master
server1:182.92.97.73 salve
2、myslq版本：
mysql -V 命令查看MySQL数据库的版本，两台服务器版本一致，不知版本不一致是否有问题。
3、连接
查看是否能ping通另外一台服务器
4、关闭防火墙
1:查看防火状态
systemctl status firewalld
service iptables status
2:暂时关闭防火墙
systemctl stop firewalld
service iptables stop
3:永久关闭防火墙
systemctl disable firewalld
chkconfig iptables off
4:重启防火墙
systemctl enable firewalld
service iptables restart
二、进行配置
1、修改主服务器master:
#vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin //[必须]启用二进制日志
server-id=212 //[必须]服务器唯一ID，默认是1，一般取IP最后一段
【查看日志是否开启】
mysql> show global variables like "%log%";
log_bin | ON
【查看server相关信息】
mysql> show global variables like "%server%";
+---------------------------------+--------------------------------------+ | Variable_name | Value | +---------------------------------+--------------------------------------+ | character_set_server | utf8mb4 | | collation_server | utf8mb4_general_ci | | innodb_ft_server_stopword_table | | | server_id | 202 | | server_id_bits | 32 | | server_uuid | eb116683-ed0d-11eb-8a01-00163e361c72 | +---------------------------------+--------------------------------------+ 6 rows in set (0.00 sec)
2、修改从服务器slave:
#vi /etc/my.cnf
[mysqld] log-bin=mysql-bin //[不是必须]启用二进制日志 server-id=73 //[必须]服务器唯一ID，默认是1，一般取IP最后一段 read-only=1 // [不是必须]，从库开启只读模式
3、重启两台服务器的mysql
/etc/init.d/mysqld restart
4、在主服务器上建立帐户并授权slave:
百分号表示所有客户端都可能连，
mysql>GRANT REPLICATION SLAVE ON *.* to '用户名'@'%' identified by '密码';
如果是mysql8之后需要先创建用户，之后才能授权：
CREATE USER '用户名'@'%' IDENTIFIED BY '密码'; grant replication slave on *.* to 用户; FLUSH PRIVILEGES;
查看用户的权限(包含REPLICATION才可以)
mysql> show grants for 'zhixi'@'%'; +-----------------------------------------------+ | Grants for zhixi@% | +-----------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO `zhixi`@`%` | +-----------------------------------------------+ 1 row in set (0.00 sec)
5、登录主服务器的mysql，查询master的状态
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000015 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
注意：到这一步先不要动主机MYSQL，防止主服务器状态值变化
6、配置从服务器Slave：
mysql>change master to master_host='182.92.209.212',master_user='刚刚创建的用户名',master_password='密码',master_log_file='mysql-bin.000015',master_log_pos=154;
如果这一步出错了，去到最后先取消主从复制，再接着继续操作！
//启动从服务器复制功能:
mysql> start slave;
7、检查从服务器复制功能状态：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 182.92.209.212
Master_User: zhixi
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000015
Read_Master_Log_Pos: 154
Relay_Log_File: zhizuo-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000015
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: 528
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: 202
Master_UUID: eb116683-ed0d-11eb-8a01-00163e361c72
Master_Info_File: /www/server/data/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)
注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。
以上主从复制搭建完成！
8、主从服务器测试：
主服务器Mysql，建立数据库，并在这个库中建表插入一条数据：
mysql> create database db01; Query OK, 1 row affected (0.00 sec) mysql> use db01; Database changed mysql> create table db_tb(id int(3),name char(10)); Query OK, 0 rows affected (0.02 sec) mysql> insert into db_tb values(001,'bobu'); Query OK, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | bjpan | | daishua_zhangzhi | | db01 | | mybd | | mysql | | oss_zhangzhixi | | performance_schema | | sys | | wordpress | +--------------------+ 10 rows in set (0.00 sec)
从机查看主机插入的数据是否过来：
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db01 | | hi_db | | hi_db1 | | hi_db2 | | mysql | | performance_schema | | sys | +--------------------+ 8 rows in set (0.00 sec) mysql> use db01; Database changed mysql> show tables; +----------------+ | Tables_in_db01 | +----------------+ | db_tb | +----------------+ 1 row in set (0.00 sec) mysql> select * from db_tb; +------+------+ | id | name | +------+------+ | 1 | bobu | +------+------+ 1 row in set (0.00 sec)
成功！
三、取消主从复制
