主从复制搭建

一、准备环境
1、配置IP地址
master 192.168.100.10
slave 192.168.100.20

2、master和slave上都安装好MySQL5.7
安装过程略

二、修改配置文件
1、master
cat /etc/my.cnf

[client]
port = 3306
socket = /usr/local/mysql/mysql.sock
default-character-set=utf8mb4

[mysqld]
server_id=10
port = 3306
user = mysql
character-set-server = utf8
default_storage_engine = innodb
log_timestamps = SYSTEM
socket = /usr/local/mysql/mysql.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
pid-file = /usr/local/mysql/mysql.pid
log_error = /usr/local/mysql/log/mysql-error.log?
skip-name-resolve
log_bin = /usr/local/mysql/data/master-bin
log_slave_updates = true

/etc/init.d/mysqld restart

Shutting down MySQL.. SUCCESS! 
Starting MySQL.sed: -e expression #1, char 21: unknown option to `s'
. SUCCESS! 

2、slave
cat /etc/my.cnf

[client]
port = 3306
socket = /usr/local/mysql/mysql.sock
[mysqld]
server_id=11
port = 3306
user = mysql
character-set-server = utf8mb4
default_storage_engine = innodb
log_timestamps = SYSTEM
socket = /usr/local/mysql/mysql.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
pid-file = /usr/local/mysql/mysql.pid
log_error = /usr/local/mysql/log/mysql-error.log?
skip-name-resolve
slow_query_log=1  
long_query_time=0.01
log_queries_not_using_indexes
relay_log=relay-log-bin
relay_log_index=slave-relay-bin.index

/etc/init.d/mysqld restart

Shutting down MySQL.. SUCCESS! 
Starting MySQL.sed: -e expression #1, char 21: unknown option to `s'
. SUCCESS! 

三、在master上创建主从同步的用户及授权
mysql -uroot -p
mysql> grant replication slave on . to ‘rep’@’%’ identified by ‘123456’;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

四、在master上做全备
1.对主数据库锁表只读后进行备份
mysql> flush table with read lock;
mysql> show variables like ‘%timeout%’; 默认情况下，自动解锁的时长参数值设置

2.锁表后查看主库的状态
mysql> show master status;

+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      588 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.导出主库数据
新开一个SSH窗口
mkdir -p /server/backup
mysqldump -uroot -p --opt --all-databases |gzip > /server/backup/bak_all_database_$(date +%F).sql.gz
Enter password:
ls /server/backup/
bak_all_database_2020-02-19.sql.gz

为了确保导出数据期间，数据库没有数据插入，导库完毕后可以再检查下主库的状态信息
mysql> show master status;

+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      588 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

解锁主库
mysql> unlock tables;

4.把从主库导出的MySQL数据迁移到从库

scp -rp /server/backup/bak_all_database_2020-02-19.sql.gz /tmp/

五、从库恢复从主库上的全备
zcat /tmp/bak_all_database_2020-02-19.sql.gz |mysql -uroot -p
Enter password:
mysql -uroot -p -e ‘show databases;’ #查看与master上的库达到一致

六、从库中进行主从同步的设置
mysql -uroot -p
mysql> change master to master_host=‘192.168.100.10’,
master_user=‘rep’,
master_password=‘123456’,
master_log_file=‘master-bin.000001’,
master_log_pos=588;
mysql>
mysql> start slave; #开启同步

七、检测同步是否成功
方法一：在从库上查看slave的状态，出现IO线程和SQL线程YES则成功
mysql> show slave status\G;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

方法二：
master上新建TestDB库，slave马上就有则成功