Percona mysql5.7部署及主从同步

一.服务部署安装
查看是否有安装服务，进行卸载
rpm -qa|grep mariadb
rpm -qa|grep mysql

rpm -e --nodeps mariadb-libs-5.5.60-1.el7_5.x86_64

安装yum源包
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum update percona-release
安装mysql服务
yum module disable mysql
yum install Percona-Server-server-57
配置数据路径
mkdir -p /data/mysql/{mysql_data,mysql_log}
chown -R mysql:mysql /data/mysql
usermod -d /data/mysql mysql
sed -i 's#/var/run/mysqld/mysqld.pid#/data/mysql/mysql_log/mysql.pid#g' /etc/systemd/system/mysql.service
sed -i 's#/var/run/mysqld/mysqld.pid#/data/mysql/mysql_log/mysql.pid#g' /usr/lib/systemd/system/mysqld.service
sed -i 's#LimitNOFILE = 5000#LimitNOFILE = 100000#g' /etc/systemd/system/mysql.service
sed -i 's#LimitNOFILE = 5000#LimitNOFILE = 100000#g' /usr/lib/systemd/system/mysqld.service
替换my.cnf文件，加载进程，确认状态
systemctl daemon-reload
systemctl status mysqld
systemctl start mysqld
systemctl enable mysqld
获取初始密码
grep -i 'password' /data/mysql/mysql_log/error.log

二.修改密码和授权
进入mysql修改初始密码
root@mysqld 13:48: [(none)]> ALTER USER USER() IDENTIFIED BY 'UsHtCT=HtCT';
root@mysqld 13:48: [(none)]> use mysql;
授权
root@mysqld 13:52: [mysql]> grant all privileges on *.* to 'root'@'%' identified by 'UsHtCT=HtCT';
root@mysqld 13:52: [mysql]> flush privileges;
root@mysqld 13:52: [mysql]> grant all privileges on *.* to 'root'@'localhost' identified by 'UsHtCT=HtCT';
root@mysqld 13:52: [mysql]> flush privileges;
设置密码永不过期
root@mysqld 13:52: [mysql]> ALTER USER 'root'@'%' PASSWORD EXPIRE NEVER;
root@mysqld 13:54: [mysql]> flush privileges;
root@mysqld 13:54: [mysql]> ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
root@mysqld 13:54: [mysql]> flush privileges;

三.主从设置
主，配置id和开启bin-log
server-id=1
log-bin=/data/mysql/mysql_data/mysql-bin

从，配置id和打开slave的relaylog
server-id = 2
relay-log-index = /data/mysql/mysql_data/slave-relay-bin.index
relay-log = /data/mysql/mysql_data/slave-relay-bin

主，创建同步用户并授权
root@mysqld 14:30: [(none)]> use mysql;
root@mysqld 14:31: [mysql]> create user 'repl' identified by 'repl=UsTaHtCT22';
root@mysqld 14:32: [mysql]> grant replication slave on *.* to 'repl'@'%' identified by 'repl=UsTaHtCT22';
root@mysqld 14:38: [mysql]> flush privileges;
root@mysqld 14:38: [mysql]> ALTER USER 'repl'@'%' PASSWORD EXPIRE NEVER;
root@mysqld 14:38: [mysql]> flush privileges;

查看主的状态
root@mysqld 14:44: [(none)]> show master status;\
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000001 | 849 | | | edf079ce-16af-11ec-a540-506b8db82649:1-14 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

查看从的状态和配置从
root@mysqld 14:45: [(none)]> show slave status\G
Empty set (0.00 sec)

root@mysqld 14:45: [(none)]> change master to
-> master_host='192.168.1.101',
-> master_port=3306,
-> master_user='repl',
-> master_password='repl=UsTaHtCT22',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=849;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

开启从和查看从的状态
root@mysqld 14:50: [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

root@mysqld 14:50: [(none)]> show slave status\G