MYSQL主从复制

开放端口号

firewall-cmd --zone=public --add-port=3306/tcp --permanent

systemctl restart firewalld.service

关闭防火墙 
systemctl stop firewalld
systemctl disable firewalld


主库配置
-------------------------------
vim /etc/my.cnf

server-id=1
#是否只读
read-only=0 

systemctl restart mysql


###[CREATE USER  'itcast'@'%' IDENTIFIED WITH mysql_native_password BY '1234']

mysql> CREATE USER 'itcast'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.04 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000011 |      415 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.05 sec)

mysql> 

从库设置
-------------------------------------
vim /etc/my.cnf

server-id=2
read-only=1

systemctl restart mysqld
 
8.0.23的语法

CHANGE PEPLICATION SOURCE TO SOURCE_HOST='XXX.XXX' ,SOURCE_USER='',SOURCE_PASSWORD='',
SOURCE_LOG_FILE='XXX',SOURCE_LOG_POS=XXX;

8.0.23之前的语法 
mysql> CHANGE MASTER TO MASTER_HOST='118.89.112.205', MASTER_USER='itcast', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=415;
Query OK, 0 rows affected, 8 warnings (0.04 sec)

mysql> 

开启同步操作 
start replica;  8.0.22之后
start slave;  8.0.22之前


mysql> start replica;
Query OK, 0 rows affected (0.03 sec)

mysql> 


查看主从同步状态
show replica status;    #8.0.22之后
show slave status;      # 8.0.22之前

mysql> show replica status\G;
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 118.89.112.205
Source_User: itcast
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: mysql-bin.000011
Read_Source_Log_Pos: 415
Relay_Log_File: VM-4-17-centos-relay-bin.000002
Relay_Log_Pos: 320
Relay_Source_Log_File: mysql-bin.000011
Replica_IO_Running: Yes
Replica_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_Source_Log_Pos: 415
Relay_Log_Space: 539
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 1
Source_UUID: 6d0970b5-abb1-11ee-9d1a-525400d7ebcd
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 0ec35786-ba6a-11ee-b11a-b8cef68a828e:1-15
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)

 

 -------主库

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_test |
| house |
| mysql |
| #mysql50#mysql-bin |
| performance_schema |
+--------------------+
6 rows in set (0.05 sec)

mysql> create database db01;
Query OK, 1 row affected (0.03 sec)

mysql> use db01;
Database changed

mysql> create table tb_user(
-> id int(11) primary key not null auto_increment,
-> name varchar(29) not null,
-> sex varchar(1)
-> )engine=innodb default charset=utf8mb4;
Query OK, 0 rows affected (0.05 sec)

mysql>

mysql> insert into tb_user values(null,'tmomca','1'),(null,'GSEnes','2'),(null,'fkaet','3');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0