mysql主从复制

一．回顾：

scp  mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz root@192.168.2.44:~

[root@mysql004 ~]#  tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz

[root@mysql004 ~]# cp -r mysql-8.0.33-linux-glibc2.12-x86_64 /usr/local/mysql

[root@mysql004 ~]# cd /usr/local/mysql/

[root@mysql004 mysql]# ls

bin   include  LICENSE  README  support-files

docs  lib      man      share

[root@mysql004 mysql]# mkdir mysql-files

[root@mysql004 mysql]# id mysql

id: mysql: no such user

[root@mysql004 mysql]# useradd -r -s /sbin/nologin mysql

[root@mysql004 mysql]# id mysql

uid=997(mysql) gid=995(mysql) 组=995(mysql)

[root@mysql004 mysql]# chown mysql:mysql /usr/local/mysql/mysql-files/

[root@mysql004 mysql]# chmod 750 /usr/local/mysql/mysql-files/

[root@mysql004 mysql]# ll

[root@mysql004 mysql]# rm -rf /etc/my.cnf

[root@mysql004 mysql]# ./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/

[root@mysql004 mysql]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data/

[root@mysql004 mysql]# cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysql8

[root@mysql004 mysql]# service mysql8 start

Starting MySQL.Logging to '/usr/local/mysql/data/mysql004.err'.

.. SUCCESS!

[root@mysql004 mysql]# /usr/local/mysql/bin/mysql -pgPq,1bAQG=Ho

mysql> set password='531242@Tt';

Query OK, 0 rows affected (0.01 sec)

设置开机自启动

chkconfig --add mysql8

chkconfig --list

vim  ./my.cnf

[mysqld]

basedir=/usr/local/mysql8

datadir=/usr/local/mysql/data

socket=/tmp/mysql.sock

service mysql8 restart

/usr/local/mysql/bin/mysql_secure_installation

将mysql的bin也添加到$PATH中

sed -i ‘$aexport PATH=/usr/local/mysql/bin/:$PATH’ /etc/profile

sed -n ‘$p’ /etc/profile

source /etc/profile

mysql

二．mysql主从复制(备份)

备份的类型：

1. 热备份
2. 逻辑备份
3. 物理备份

环境准备

Master   192.168.2.80       主服务器

Slave     192.168.2.90       从服务器

主从复制核心思路：

时间同步是非常重要的设置

两台机器下载安装ntpdate.x86_64

yum -y install ntpdate.x86_64

ntpdate cn.ntp.org.cn

1. 上传mysql安装包到mstaer和slave

scp mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz root@192.168.2.80:~

    

scp mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz root@192.168.2.90:~

1. 编写脚本

vim mysql.sh

#!/bin/bash

yum list installed |grep libaio

if [ $? ne 0 ]; then

yum -y install libaio

fi

echo libaio yes

rm -rf /etc/my.cnf

echo remo my.cnf yes

tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz

echo tar zx yes

cp -r ~/mysql-8.0.33-linux-glibc2.12-x86_64 /usr/local/mysql

echo copy file to /usr/local/mysql   yes

mkdir /usr/local/mysql/mysql-files

echo mysql-files yes

grep mysql /etc/passwd

useradd -r -s /sbin/nologin mysql

chown mysql:mysql /usr/local/mysql/mysql-files

chmod 750 /usr/local/mysql/mysql-files

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/

/usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8

sed -i '$aexport PATH=/usr/local/mysql/bin:$PATH' /etc/profile

source /etc/profile

3. 编写配置⽂件

vim /usr/local/mysql/my.cnf

[mysqld]

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

socket=/tmp/mysql.sock

port=3306

log-error=/usr/local/mysql/data/db01-

master.err

log-bin=/usr/local/mysql/data/binlog

server-id=10

character_set_server=utf8mb4

4. 重启服务

service mysql8 restart

5. 设置开机启动

[root@mysql001 ~]# chkconfig --add

mysql8

[root@mysql001 ~]# chkconfig mysql8 on

[root@mysql001 ~]# chkconfig

slave从服务器软件的安装

1. 不需要初始化

相对于主服务器的安装与配置，从服务器不需要初始化，他的数据都来⾃于主服务器，其他都⼀样

[root@slave ~]# ls

anaconda-ks.cfg mysql-8.0.33-linux-glibc2.12-

x86_64.tar.xz

initserver.sh mysql.sh

3. 脚本

vim mysql.sh

#!/bin/bash

yum list installed |grep libaio

if [ $? ne 0 ]; then

yum -y install libaio

fi

echo libaio yes

rm -rf /etc/my.cnf

echo remo my.cnf yes

tar -xf mysql-8.0.33-linux-glibc2.12-

x86_64.tar.xz

cp -r ~/mysql-8.0.33-linux-glibc2.12-x86_64

/usr/local/mysql

echo copy file to /usr/local/mysql yes

mkdir /usr/local/mysql/mysql-files

echo mysql-files yes

grep mysql /etc/passwd

useradd -r -s /sbin/nologin mysql

chown mysql:mysql /usr/local/mysql/mysql-files

chmod 750 /usr/local/mysql/mysql-files

#/usr/local/mysql/bin/mysqld --initialize --

user=mysql --basedir=/usr/local/mysql/

cp /usr/local/mysql/support-files/mysql.server

/etc/init.d/mysql8

sed -i '$aexport PATH=/usr/local/mysql/bin:$PATH' /etc/profile

source /etc/profile

4. 配置⽂件

[root@slave ~]# ls /usr/local/mysql/

bin include LICENSE my.cnf README

support-files

docs lib man mysql-files share

vim /usr/local/mysql/my.cnf

[mysqld]

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

socket=/tmp/mysql.sock

port=3310

log-error=/usr/local/mysql/data/db01-slave.err

relay-log=/usr/local/mysql/data/relaylog

server-id=11

character_set_server=utf8mb4

主从复制之数据同步

1. master服务器同步到slave服务器

1. 停⽤master服务器msyql服务

service mysql8 stop

2. master删除/usr/local/mysql/data/auto.cnf 每安装⼀个

mysql软件，data数据⽬录都会产⽣⼀个auto.cnf⽂件，⾥⾯

是⼀个唯⼀性编号，相当于每个⼈的身份证编号

[root@mysql001 ~]# ls

/usr/local/mysql/data/auto.cnf

/usr/local/mysql/data/auto.cnf

[root@mysql001 ~]# cat

/usr/local/mysql/data/auto.cnf

[auto]

server-uuid=f6421989-5330-11ef-974f-

000c29ce78bb

[root@mysql001 ~]# rm -rf

/usr/local/mysql/data/auto.cnf

3. master和slave都安装rsync

yum -y install rsync

4. 同步master中的data到slave对应位置

rsync -av /usr/local/mysql/data

root@10.1.1.110:/usr/local/mysql/

5. 启动主服务器和从服务器

[root@mysql001 ~]# service mysql8 start

Starting MySQL.. SUCCESS!

[root@slave ~]# service mysql8 start

Starting MySQL.Logging to

'/usr/local/mysql/data/db01-slave.err'.

. SUCCESS!

6. 登录从服务器

/usr/local/mysql/bin/mysql -P3310 -

pZhang_Min_666

⽆法启动的⼏种情况

1. /etc/init.d/mysql8不存在，可能没有复制

2. mysql没有安装在/usr/local/mysql

3. my.cnf导致⽆法启动，格式

1. 安装路径

2. 数据⽬录

3. 套接字

4. 错误⽇志到底放在data⾥头

5. mysql账户⽆法写⼊/usr/local/mysql/

6. 将错误⽇志放在data就没有权限⽂件

7. 直接修改/usr/local/mysql的权限

主从复制的实现

1. master创建授权账号

2. 在master中锁表，然后查看⼆进制⽂件和位置

mysql> create user 'slave'@'10.1.1.%'

identified by 'slave_123';

Query OK, 0 rows affected (0.02 sec)

mysql> grant replication slave on *.* to

'slave'@'10.1.1.%';

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

2. 在master中锁表，然后查看⼆进制⽂件和位置

mysql> flush tables with read lock;

mysql> show master status;

+---------------+----------+--------------+----

--------------+-------------------+

| File | Position | Binlog_Do_DB |

Binlog_Ignore_DB | Executed_Gtid_Set |

+---------------+----------+--------------+----

--------------+-------------------+

| binlog.000003 | 707 | |

| |

+---------------+----------+--------------+----

--------------+-------------------+

1 row in set (0.00 sec)

3. 在从服务器中，使⽤change master to 指定主服务器，并实现数

据同步

[root@slave ~]# /usr/local/mysql/bin/mysql -

P3310 -pZhang_Min_666

mysql: [Warning] Using a password on the

command line interface can be insecure.

Welcome to the MySQL monitor. Commands end

with ; or \g.

Your MySQL connection id is 8

Server version: 8.0.33 MySQL Community Server -

GPL

Copyright (c) 2000, 2023, Oracle and/or its

affiliates.

Oracle is a registered trademark of Oracle

Corporation and/or its

affiliates. Other names may be trademarks of

their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to

clear the current input statement.

mysql> change master to

-> master_host='10.1.1.100',

-> master_user='slave',

-> master_password='slave_123',

-> master_port=3306,

-> master_log_file='binlog.000003',

-> master_log_pos=707;

Query OK, 0 rows affected, 9 warnings (0.01

sec)

change master to master_host='10.1.1.100',

master_user='slave',

master_password='slave_123', master_port=3306,

master_log_file='binlog.000004',

master_log_pos=1092;

4. 启动slave数据同步，查看状态

mysql> start slave；

mysql> show slave status\G

5. 常⻅⽂件

1. change-master-to写错

6. 解锁⽂件

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

8.0需要ssl⾮对称加密

1. 获得远程master主机的公钥

mysql -uzhangmin -pabc_123 -h 192.168.71.166 -

P3306 --get-server-public-key

quit

1. 登录slave服务器本地的数据库

mysql -pZhang_Min_666 -P3310

3. 停⽤slave服务，重新设置slave服务

mysql> stop slave;

mysql> reset slave;

mysql> change master to

master_host='192.168.71.166',

master_user='zhangmin',

master_password='abc_123', master_port=3306,

master_log_file='binlog.000006',master_log_pos= 866;

4. 启动slave服务

mysql> start slave

mysql> show slave status\G