mysql 5.7主从复制配置

集群规模一主一从

数据库安装

tar xf mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz 
groupadd mysql
useradd -M -s /sbin/nologin -g mysql mysql
mv mysql-5.7.21-linux-glibc2.12-x86_64 mysql
chown -R mysql.mysql mysql

#主节点配置文件：vim /etc/my.cnf

[mysqld]
bind-address=0.0.0.0
port=3306
user=mysql
basedir=/home/mysql
datadir=/home/mysql/data
log-error=/home/mysql/data/mysql.err
socket=/tmp/mysql.sock
pid-file=/home/mysql/data/mysql.pid

server-id=1

slow_query_log=ON
slow_query_log_file=/home/mysql/data/slow.log
long_query_time=1

gtid_mode=ON
enforce_gtid_consistency=ON

binlog_format=row
expire_logs_days=7
max_binlog_size=512M
log-bin=/home/mysql/logs/mysql-bin
log_bin_index=/home/mysql/logs/mysql-bin.index
log-slave-updates = ON

character_set_server=utf8mb4

创建日志目录，否则初始化会报错
mkdir /home/mysql/logs
初始化数据库
mysqld --defaults-file=/etc/my.cnf --basedir=/home/mysql --datadir=/home/mysql/data --user=mysql --initialize

cp support-files/mysql.server /etc/init.d/mysqld
/etc/init.d/mysqld start

#查看复制初始密码
cat /home/mysql/data/mysql.err
#登录并重新设置密码
mysql -uroot -p

mysql>set password=password('新密码')；

#从节点配置文件，注意server-id不能相同：vim /etc/my.cnf

[mysqld]
bind-address=0.0.0.0
port=3306
user=mysql
basedir=/home/mysql
datadir=/home/mysql/data/
log-error=/home/mysql/data/mysql.err
socket=/tmp/mysql.sock
pid-file=/home/mysql/data/mysql.pid

server-id=151

slow_query_log=ON
slow_query_log_file=/home/mysql/data/slow.log
long_query_time=1

gtid_mode = ON
enforce_gtid_consistency = ON
skip-slave-start = true
expire_logs_days = 7
max_binlog_size  = 512M
read_only = ON

log-bin=/home/mysql/logs/mysql-bin
log_bin_index = /home/mysql/logs/mysql-bin.index
relay-log = /home/mysql/logs/relay-log
relay-log-index = /home/mysql/logs/relay-log-index
relay-log-info-file = /home/mysql/logs/relay-log.info
log-slave-updates=true
master-info-repository = table
relay-log-info-repository = table

#character config
character_set_server=utf8mb4
symbolic-links=0

主从配置，主节点配置同步使用的账号密码，从节点指定主节点

主节点操作

mysql> GRANT REPLICATION SLAVE ON *.* to 'repl'@'192.168.208.%' IDENTIFIED BY 'repl';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003 |      973 |              |                  | 11642011-37c3-11ec-b310-0cda411d7139:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.01 sec)

从节点操作

mysql> CHANGE MASTER TO MASTER_HOST='192.168.208.150',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G;
……
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
……