MYSQL双机主从搭建

下载安装包到home下
wget -P /home https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.38-1.el7.x86_64.rpm-bundle.tar
tar -xvf mysql-5.7.38-1.el7.x86_64.rpm-bundle.tar
ls /home/

卸载mariadb
rpm -qa | grep mariadb
rpm -e mariadb-libs-5.5.60-1.el7_5.x86_64 --nodeps

安装MySQL
cd /home/
yum install ./*.rpm

#启动并设置开机自启
systemctl start mysqld
systemctl enable mysqld
systemctl status mysqld

#查看版本
mysql --version
mysql  Ver 14.14 Distrib 5.7.38, for Linux (x86_64) using  EditLine wrapper

#查看初始root密码，并重新设置
grep 'temporary password' /var/log/mysqld.log
2022-10-11T03:24:23.926690Z 1 [Note] A temporary password is generated for root@localhost: +ejyY)mTH0di

#初始密码:+ejyY)mTH0di
+ejyY)mTH0di

#登录mysql，并重新设置root密码
mysql -u root -p

#设置root密码是Admin_2022
mysql> set password for root@localhost = password('Admin_2022');

#查看当前数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

#设置允许远程
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select user,host from user;

mysql> update user set host="%" where user="root";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

查看配置文件
cat /etc/my.cnf

停止MySQL服务，拷贝文件到新建/data/下
systemctl status mysqld
systemctl stop mysqld
mkdir /data/
cp -arp /var/lib/mysql /data/
mv /var/lib/mysql /var/lib/mysqlbak

修改配置文件
vim /etc/my.cnf
datadir=/data/mysql
socket=/data/mysql/mysql.sock

重启MySQL
systemctl restart mysqld
systemctl status mysqld

cat >>/etc/my.cnf<<EOF
server-id = 1 
log-bin = mysql-bin    #主从服务的核心 定义binblog日志的前缀名
#binlog-do-db=test      #需要同步的数据库，如果没有本行，即表示同步所有的数据库
#binlog-do-db=mydb      #需要同步的数据库，同时同步test、mydb库
binlog-ignore-db=mysql #被忽略的数据库
EOF

生效需要重启MySQL，等会后面再重启

mysql -u root -p

mysql> GRANT REPLICATION SLAVE ON *.* TO 'mysync'@'192.168.1.147' IDENTIFIED BY '12345678';
mysql> flush privileges;

重启一下服务，生效my.cnf配置
systemctl restart mysqld

查询下当前状态
mysql> show master status;

查看配置文件
cat /etc/my.cnf

停止MySQL服务，拷贝文件到新建/data/下
systemctl status mysqld
systemctl stop mysqld
mkdir /data/
cp -arp /var/lib/mysql /data/
mv /var/lib/mysql /var/lib/mysqlbak

修改配置文件
vim /etc/my.cnf
datadir=/data/mysql
socket=/data/mysql/mysql.sock

重启MySQL
systemctl restart mysqld
systemctl status mysqld

cat >>/etc/my.cnf<<EOF
server-id = 2 
log-bin = mysql-bin    #主从服务的核心 定义binblog日志的前缀名
#binlog-do-db=test      #需要同步的数据库，如果没有本行，即表示同步所有的数据库
#binlog-do-db=mydb      #需要同步的数据库，同时同步test、mydb库
binlog-ignore-db=mysql #被忽略的数据库
EOF
生效需要重启MySQL，等会后面再重启

mysql -u root -p

mysql> GRANT REPLICATION SLAVE ON *.* TO 'mysync'@'192.168.1.146' IDENTIFIED BY '12345678';
mysql> flush privileges;

重启MySQL
systemctl restart mysqld
systemctl status mysqld


配置服务
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.146',MASTER_USER='mysync',MASTER_PASSWORD='Admin_2022',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;

开启服务
mysql> start slave;
mysql> show slave status;

先启动主服务器MySQL:systemctl start mysqld
然后启动从服务器MySQL:systemctl start mysqld
进入从服务器开启从服务:mysql> start slave;

先关闭从服务器的同步：mysql> slave stop;
然后停止从服务器MySQL:systemctl stop mysqld
然后停止主服务器MySQL:systemctl stop mysqld