一、环境准备
系统版本：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最后一段
3、重启两台服务器的mysql
/etc/init.d/mysqld restart
4、在主服务器上建立帐户并授权slave:
百分号表示所有客户端都可能连
mysql>GRANT REPLICATION SLAVE ON *.* to '用户名'@'%' identified by '密码';
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)
成功！
三、取消主从复制
