centos上mysql 8.x - 主从复制

主从

// 重装mysql==========================================

---

stop：

systemctl stop mysqld

list：

yum list installed mysql*
或者
rpm -qa|grep -i mysql

remove：

rpm -e --nodeps mysql-community-client.x86_64
rpm -e --nodeps mysql-community-common.x86_64
rpm -e --nodeps mysql-community-libs.x86_64
rpm -e --nodeps mysql-community-libs-compat.x86_64
rpm -e --nodeps mysql-community-server.x86_64
rpm -e --nodeps mysql-community-client-plugins.x86_64

install：

rpm -ivh ./*.rpm

start：

systemctl enable mysqld
systemctl start mysqld

创建账号：

name=root
passwd=passwd123456

OLDPWD=`cat /var/log/mysqld.log |grep 'temporary password'|tail -1 |awk -F"host: " '{print $2}'`
mysql  --connect-expired-password -uroot -p"$OLDPWD" -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'passwd123456';"
mysql -uroot -ppasswd123456 -e "CREATE USER '"$name"'@'%' IDENTIFIED WITH mysql_native_password BY '"$passwd"';GRANT ALL PRIVILEGES ON *.* TO '"$name"'@'%' WITH GRANT OPTION;"

创建表：

passwd=passwd123456
mysql -uroot -p$passwd -e "CREATE DATABASE data DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;"
mysql -uroot -p$passwd --default-character-set=utf8 data<  data.sql

主库：187
从库：188

mysql 8.x

= = =【主】=========================================================
1.登录master，创建从节点使用的账号并授权：

mysql> CREATE USER 'copy'@'192.168.1.188' IDENTIFIED WITH mysql_native_password BY 'passwd123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'copy'@'192.168.1.188';

2.刷新权限： flush privileges;
3.查看状态：

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000010 |      848 | media        |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

4.mysql结果格式化显示，末尾加 \G

mysql> SHOW MASTER STATUS\G;
*************************** 1. row ***************************
             File: binlog.000003
         Position: 64742621
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql>

= = =【从】==========================================================

1.设置主节点参数

mysql> CHANGE MASTER TO
MASTER_HOST='192.168.1.187',
MASTER_USER='copy',
MASTER_PASSWORD='passwd123456',
MASTER_LOG_FILE='mysql-bin.000010',
MASTER_LOG_POS=848 ;
Query OK, 0 rows affected, 2 warnings (0.34 sec)

2.查看状态：show slave status \G
3.开启从节点：start slave;

4.查看serverid：show variables like ‘server_id’;
注意：serverid唯一

---

*重要：my.cnf

指定需要复制的库，可以配置多行以指定多个库
binlog-do-db=

导入数据库：
1.创建数据库：create database databaseName;
2.使用数据库：use databaseName;
3.导入： source ./databaseName.sql;

查看参数说明：
mysqld --verbose --help

二、备份数据，主从重做

1.修改主库端口，重启，防止新插入数据
2.查看主库Position File ：

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000010 |      848 | media        |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

3.备份主库

mysqldump -u 用户名 -p -B 数据库名1 数据库名2 > 备份文件名.sql

4.推到从库服务器

scp mysql.bak.sql root@192.168.128.101:/tmp/

5.停止从库slave

mysql> stop slave;

6.修改从库为其他端口，重启，防止新插入数据
7.删除从库全部数据
8.导入mysql.bak.sql

mysql> source /tmp/mysql.bak.sql

9.重做主从（文件名和POS获取自之前的主库 SHOW MASTER STATUS;）

mysql> CHANGE MASTER TO
MASTER_HOST='192.168.1.187',
MASTER_USER='copy',
MASTER_PASSWORD='passwd123456',
MASTER_LOG_FILE='mysql-bin.000010',
MASTER_LOG_POS=848 ;
Query OK, 0 rows affected, 2 warnings (0.34 sec)

10.查看从库状态，见两个YES且库名正确即为正常

mysql> show slave status \G