mysql_5_7_24主从搭建

mysql_5_7_24主从搭建一：主机配置信息

二：主从配置文件信息

主从都开启二进制日志

1，主库

2，从库

三：主库新建复制名

create user repl@'%' identified by ‘123456’;

grant replication salve on *.* to repl@'%';

四：主库进行热备份

mysqldump -uroot -p123456 --socket=/data/mysql/datadir/3307/data/mysql.sock

-A -R --triggers -E --master-data=2 --single-transaction > /home/mysql/backup/3307/3307_full.sql

[root@master ~]# cd /home/mysql/backup

[root@master ~]# vi 3307_full.sql

找到如下一行并拷贝出来

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000035', MASTER_LOG_POS=494;

五：从库恢复

[root@slave ~]# mysql -uroot -p123456 -S /data/3307/mysql.sock

mysql> set sql_log_bin=0

mysql> source /home/mysql/backup/3307/3307_full.sql;

mysql> set sql_log_bin=1

六：从库配置恢复参数

不知道命令语法格式的可以参考：mysql> help change master to; 帮助命令mysql> CHANGE MASTER TO

MASTER_HOST='192.168.1.13',

MASTER_USER='repl',

MASTER_PASSWORD='123456',

MASTER_PORT=3306,

MASTER_LOG_FILE='mysql-3306.000001',

MASTER_LOG_POS=763,

MASTER_CONNECT_RETRY=10;

七：从库启动复制进程

mysql>start slave；

八：查看复制进程状态信息

1，主库

mysql> show processlist\G

*************************** 1. row *************************** Id: 2

User: repl

Host: 192.168.1.13:62289

db: NULL

Command: Binlog Dump

Time: 1547

State: Master has sent all binlog to slave; waiting for more updates

Info: NULL

2，从库

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.13

Master_User: repl

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: mysql-3306.000016

Read_Master_Log_Pos: 402

Relay_Log_File: rac1-relay-bin.000010

Relay_Log_Pos: 617

Relay_Master_Log_File: mysql-3306.000016

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: 402

Relay_Log_Space: 990

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: 3306

Master_UUID: 81421c4e-c027-11ea-9b40-0800279e207a

Master_Info_File: /data/3307/data/https://www.360docs.net/doc/d417271426.html,

SQL_Delay: 600

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)

九：主从复制进程之间的关系

主库：

binlog 日志

dump线程

从库：

IO线程

https://www.360docs.net/doc/d417271426.html,

tcp/ip缓存

rely-log.000001

sql线程

https://www.360docs.net/doc/d417271426.html,

十：排查错误

1，IO线程

2，SQL线程

十一：从库只读状态设置super_read_only

read_only