mysql主从

最新推荐文章于 2023-02-17 09:21:23 发布

百慕卿君

最新推荐文章于 2023-02-17 09:21:23 发布

阅读量571

点赞数

文章标签：数据库 mysql linux

本文链接：https://blog.csdn.net/yi_qingjun/article/details/109358178

版权

1.主从数据库的区别

从数据库(Slave)是主数据库的备份，当主数据库(Master)变化时从数据库要更新，这些数据库软件可以设计更新周期。这是提高信息安全的手段。主从数据库服务器不在一个地理位置上，当发生意外时数据库可以保存。

1.1 主从分工

其中Master负责写操作的负载，也就是说一切写的操作都在Master上进行，而读的操作则分摊到Slave上进行。这样一来的可以大大提高读取的效率。在一般的互联网应用中，经过一些数据调查得出结论，读/写的比例大概在 10：1左右，也就是说大量的数据操作是集中在读的操作，这也就是为什么我们会有多个Slave的原因。但是为什么要分离读和写呢？熟悉DB的研发人员都知道，写操作涉及到锁的问题，不管是行锁还是表锁还是块锁，都是比较降低系统执行效率的事情。我们这样的分离是把写操作集中在一个节点上，而读操作其其他的N个节点上进行，从另一个方面有效的提高了读的效率，保证了系统的高可用性。

1.2 基本过程

Mysql的主从同步就是当master（主库）发生数据变化的时候，会实时同步到slave（从库）。
主从复制可以水平扩展数据库的负载能力，容错，高可用，数据备份。
不管是delete、update、insert，还是创建函数、存储过程，都是在master上，当master有操作的时候，slave会快速的接受到这些操作，从而做同步。

1.3 用途和条件

mysql主从复制用途

实时灾备，用于故障切换
读写分离，提供查询服务
备份，避免影响业务

主从部署必要条件：

主库开启binlog日志（设置log-bin参数）
主从server-id不同
从库服务器能连通主库

1.4 主从形式

一主一从
主主复制
一主多从—扩展系统读取的性能，因为读是在从库读取的
多主一从—5.7开始支持
联级复制

2. 主从复制原理

在这里插入图片描述

在master机器上的操作：
当master上的数据发生变化时，该事件变化会按照顺序写入bin-log中。当slave链接到master的时候，master机器会为slave开启binlog dump线程。当master的binlog发生变化的时候，bin-log dump线程会通知slave，并将相应的binlog内容发送给slave。
在slave机器上操作:

从库生成两个线程，一个I/O线程，一个SQL线程
- I/O线程去请求主库的binlog，并将得到的binlog日志写到relay log（中继日志）文件中
- SQL线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，达到最终数据一致的目的

主从复制过程：

MySQL之间数据复制的基础是二进制日志文件（binlog）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有写操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。

2.1 三种主要实现粒度

详细的主从同步主要有三种形式：statement、row、mixed

1. statement: 会将对数据库操作的sql语句写道binlog中

2. row: 会将每一条数据的变化写道binlog中。

3.mixed: statement与row的混合。Mysql决定何时写statement格式的binlog, 何时写row格式的binlog。

3. 主从复制配置

主从复制配置步骤：

确保从数据库与主数据库里的数据一样
在主数据库里创建一个同步账号授权给从数据库使用
配置主数据库（修改配置文件）
配置从数据库（修改配置文件）

3.1 mysql安装

主数据库和从数据库上配置本地源，二进制安装mysql，并执行以下脚本
[root@master ~]# ls
anaconda-ks.cfg  mysql
[root@master ~]# cd mysql/
[root@master mysql]# ls
install.sh  soft
[root@master mysql]# ls soft/
mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
[root@master mysql]# vim install.sh 

#!/bin/bash
installdir=/usr/local
packname=mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
unpck=$installdir/$(echo $packname| awk -F'.tar' '{print $1}')
port=3306
datadir=/data
PATH=$installdir/mysql/bin:$PATH


yum -y -q install perl  ncurses-compat-libs &>/dev/null

read -p "请输入，你要创建几个实例就输入数字几："  num
read -p "请输入，你要为数据库设置什么密码："   num1



id mysql &>/dev/null || /usr/sbin/useradd -r -M -s /sbin/nologin mysql

[ ! -d $installdir ] && mkdir -p $installdir

if [ !  -d  $unpck ];then
     echo "正在解压$packname至$installdir下"
     tar xf soft/$packname -C $installdir
fi

ln -s $unpck  $installdir/mysql &>/dev/null
chown -R mysql.mysql $installdir/mysql*
echo "export PATH=$installdir/mysql/bin:\$PATH" > /etc/profile.d/mysql.sh
if [ $num -gt 1 ];then
cat > /etc/my.cnf <<EOF
[mysqld_multi]
mysqld = $installdir/mysql/bin/mysqld_safe
mysqladmin = $installdir/mysql/bin/mysqladmin
EOF
fi




for i in $(seq $num);do
    if [ $i -ne 1 ]; then
         let port++
    fi
         mkdir -p $datadir/$port &>/dev/null
         chown -R mysql.mysql $datadir
        wcinit=$(ls $datadir/$port|wc -l)
        if [ $wcinit -eq 0 ];then
            echo "正在初始化$port实例"
            $installdir/mysql/bin/mysqld --initialize-insecure --datadir=$datadir/$port --user=mysql --explicit_defaults_for_timestamp &> /dev/null
        fi
    if [ $num -eq 1 ];then
cat > /etc/my.cnf <<EOF
[mysqld]
basedir = $installdir/mysql
datadir = $datadir/$port
socket = /tmp/mysql.sock
port = 3306
pid-file = $datadir/$port/mysql.pid
user = mysql
skip-name-resolve
EOF

       \cp -a  $installdir/mysql/support-files/mysql.server /etc/init.d/mysqld
        sed -ri "s#^(basedir=).*#\1$installdir/mysql#g"      /etc/init.d/mysqld
        sed -ri "s#^(datadir=).*#\1$datadir#g"                   /etc/init.d/mysqld
        service mysqld start
        $installdir/mysql/bin/mysql -uroot -e "set password = password('$num1');"
    else
cat >> /etc/my.cnf <<EOF

[mysqld$port]
datadir = $datadir/$port
port = $port
socket = /tmp/mysql$port.sock
pid-file = $datadir/$port/mysql_$port.pid
log-error=/var/log/$port.log
EOF
        $installdir/mysql/bin/mysqld_multi start $port
        sleep 3
        $installdir/mysql/bin/mysql -uroot -S /tmp/mysql$port.sock -e "set password = password('$num1');"
    fi
done

[root@master mysql]# bash install.sh 
请输入，你要创建几个实例就输入数字几：1
请输入，你要为数据库设置什么密码：redhat
正在解压mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz至/usr/local下
正在初始化3306实例
Starting MySQL.Logging to '/data/3306/master.err'.
 SUCCESS! 


主数据库查看
[root@master mysql]# ss -antl
State   Recv-Q  Send-Q   Local Address:Port    Peer Address:Port  
LISTEN  0       128            0.0.0.0:22           0.0.0.0:*     
LISTEN  0       80                   *:3306               *:*     
LISTEN  0       128               [::]:22              [::]:*     
[root@master ~]# which mysql
/usr/local/mysql/bin/mysql
[root@master ~]# mysql -uroot -predhat
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 3
Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> quit
Bye


从数据库查看
[root@slave ~]# which mysql
/usr/local/mysql/bin/mysql
[root@slave ~]# ss -antl
State   Recv-Q  Send-Q   Local Address:Port    Peer Address:Port  
LISTEN  0       128            0.0.0.0:22           0.0.0.0:*     
LISTEN  0       80                   *:3306               *:*     
LISTEN  0       128               [::]:22              [::]:*     
[root@slave ~]# mysql -uroot -predhat
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 3
Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> quit
Bye

3.2 mysql主从配置(从无到无)

环境说明：

主数据库： 192.168.50.131 ；无数据
从数据库： 192.168.50.135 ；无数据
两台服务器都需要安装mysql-5.7版本

3.2.1. 在主数据库里创建一个同步账号授权给从数据库使用

主数据库操作以下步骤
mysql> grant replication slave on *.* to repl@192.168.50.135 identified by 'repl123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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


两台服务器都关闭防火墙
[root@slave ~]# systemctl stop firewalld
[root@slave ~]# setenforce 0


在从数据库上，从数据库能登陆进去
[root@slave ~]# mysql -urepl -p'repl123!' -h192.168.50.131
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 5
Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> show databases;                 当前只是有授权同步功能，但还没有同步，所以只有一个数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

3.2.2. 配置主数据库

添加最后两行
[root@master ~]# vim /etc/my.cnf 

[mysqld]
basedir = /usr/local/mysql
datadir = /data/3306
socket = /tmp/mysql.sock
port = 3306
pid-file = /data/3306/mysql.pid
user = mysql
skip-name-resolve

server-id = 10                  添加这两行
log-bin = mysql_bin


重启服务
[root@master ~]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS! 
[root@master ~]# ss -antl
State   Recv-Q  Send-Q   Local Address:Port    Peer Address:Port  
LISTEN  0       128            0.0.0.0:22           0.0.0.0:*     
LISTEN  0       80                   *:3306               *:*     
LISTEN  0       128               [::]:22              [::]:*     

查看主库的状态
[root@master ~]# mysql -uroot -predhat
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 2
Server version: 5.7.31-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> show master status
    -> ;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


该日志记录到了/data/3306下面
[root@master ~]# ll /data/3306
total 122972
-rw-r-----. 1 mysql mysql       56 Oct 29 14:56 auto.cnf
-rw-------. 1 mysql mysql     1680 Oct 29 14:56 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Oct 29 14:56 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Oct 29 14:56 client-cert.pem
-rw-------. 1 mysql mysql     1676 Oct 29 14:56 client-key.pem
-rw-r-----. 1 mysql mysql      350 Oct 29 15:30 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Oct 29 15:31 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Oct 29 15:31 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Oct 29 14:56 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Oct 29 15:31 ibtmp1
-rw-r-----. 1 mysql mysql    11305 Oct 29 15:31 master.err
drwxr-x---. 2 mysql mysql     4096 Oct 29 14:56 mysql
-rw-r-----. 1 mysql mysql      154 Oct 29 15:31 mysql_bin.000001     待会同步就只同步到154
-rw-r-----. 1 mysql mysql       19 Oct 29 15:31 mysql_bin.index
-rw-r-----. 1 mysql mysql        6 Oct 29 15:31 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Oct 29 14:56 performance_schema
-rw-------. 1 mysql mysql     1680 Oct 29 14:56 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Oct 29 14:56 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Oct 29 14:56 server-cert.pem
-rw-------. 1 mysql mysql     1680 Oct 29 14:56 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Oct 29 14:56 sys

3.2.3. 配置从数据库

添加配置文件内容
[root@slave ~]# vim /etc/my.cnf 

[mysqld]
basedir = /usr/local/mysql
datadir = /data/3306
socket = /tmp/mysql.sock
port = 3306
pid-file = /data/3306/mysql.pid
user = mysql
skip-name-resolve

#添加以下两行
server-id = 20             此处的20只是个标识，主数据库上写的10，主数据库上的这个数必须小于从数据库
relay-log = myrelay              启用中继日志relay-log


重启后登陆
[root@slave ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@slave ~]# mysql -uroot -predhat
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 2
Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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='192.168.50.131',master_user='repl',master_password='repl123!',master_log_file='mysql_bin.000001',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)


查看从服务器状态
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.50.131
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: myrelay.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000001
             Slave_IO_Running: Yes         这两个线程必须都为yes，但凡一个为no，则表示不成功
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 519
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10
                  Master_UUID: e7731be3-19b3-11eb-a5c9-000c291636de
             Master_Info_File: /data/3306/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

3.2.4. 测试验证

查看两台数据库是否一致

主服务器有四个数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)


从服务器有四个数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

在主服务器上创建数据库student和表abc

mysql> create database student;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
mysql> create table abc(id int not null primary key auto_incremennt,name varchar(50),score float);
Query OK, 0 rows affected (0.01 sec)

mysql> desc abc;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | YES  |     | NULL    |                |
| score | float       | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

在从服务器上查看是否同步了

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
mysql> show tables from student;
+-------------------+
| Tables_in_student |
+-------------------+
| abc               |
+-------------------+
1 row in set (0.00 sec)

mysql> desc student.abc;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | YES  |     | NULL    |                |
| score | float       | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

在主服务器上往表格里插入数据

mysql> insert abc values(1,'tom',20),(2,'jerry',43),(3,'zhangsan'',67);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from abc;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | tom      |    20 |
|  2 | jerry    |    43 |
|  3 | zhangsan |    67 |
+----+----------+-------+
3 rows in set (0.00 sec)

在从服务器上查看数据是否同步了

mysql> use student;
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 * from abc;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | tom      |    20 |
|  2 | jerry    |    43 |
|  3 | zhangsan |    67 |
+----+----------+-------+
3 rows in set (0.00 sec)

3.3. mysql主从配置（从有到无）

环境说明：

主数据库： 192.168.50.131 ；有数据
从数据库： 192.168.50.135 ；无数据
两台服务器都需要安装mysql-5.7版本

在主数据库和从数据库上配置本地源，二进制安装mysql，并执行以下脚本部署数据库


主数据库
[root@master ~]# ls
anaconda-ks.cfg  mysql
[root@master ~]# cd mysql/
[root@master mysql]# bash install.sh 
请输入，你要创建几个实例就输入数字几：1
请输入，你要为数据库设置什么密码：redhat
正在解压mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz至/usr/local下
正在初始化3306实例
Starting MySQL.Logging to '/data/3306/master.err'.
 SUCCESS! 
[root@master mysql]# ss -antl
State   Recv-Q  Send-Q   Local Address:Port   Peer Address:Port 
LISTEN  0       128            0.0.0.0:22          0.0.0.0:*    
LISTEN  0       80                   *:3306              *:*    
LISTEN  0       128               [::]:22             [::]:*    
[root@master mysql]# bash
[root@master mysql]# mysql -uroot -predhat
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 3
Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> 



从数据库
[root@slave ~]# ls
anaconda-ks.cfg  mysql
[root@slave ~]# cd mysql/
[root@slave mysql]# bash install.sh 
请输入，你要创建几个实例就输入数字几：1
请输入，你要为数据库设置什么密码：redhat
正在解压mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz至/usr/local下
正在初始化3306实例
Starting MySQL.Logging to '/data/3306/slave.err'.
 SUCCESS! 
[root@slave mysql]# ss -antl
State   Recv-Q  Send-Q   Local Address:Port   Peer Address:Port 
LISTEN  0       128            0.0.0.0:22          0.0.0.0:*    
LISTEN  0       80                   *:3306              *:*    
LISTEN  0       128               [::]:22             [::]:*    
[root@slave mysql]# mysql -uroot -predhat
bash: mysql: command not found
[root@slave mysql]# bash                     刷新一下才能进入数据库，不然只能再新开一台终端
[root@slave mysql]# mysql -uroot -predhat
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 3
Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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>

3.3.1 主数据库创建school数据库和student表，并插入东西；从数据库不加任何东西

主数据库有school数据库和有内容的student表
[root@master mysql]# mysql -uroot -predhat
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 3
Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database school;
Query OK, 1 row affected (0.00 sec)

mysql> use school;
Database changed
mysql> create table student(id int not null primary key auto_increment,name varchar(50),age tinyint);
Query OK, 0 rows affected (0.00 sec)

mysql> insert student(name,age) values('tom',29),('jerry',24),(',zhangsan',18),('lisi',56);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | tom      |   29 |
|  2 | jerry    |   24 |
|  3 | zhangsan |   18 |
|  4 | lisi     |   56 |
+----+----------+------+
4 rows in set (0.00 sec)



从数据库没有新添加的数据库和表
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

3.3.2 确保从数据库与主数据库里的数据一样

为确保从数据库与主数据库里的数据一样，先全备主数据库并还原到从数据库中

1. 锁表，避免在备份期间有其他人在写入导致数据不一致（锁表之后别人无法添加东西，退出之后即解锁）
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)


刺客我往表格里插入wangwu，则一致卡在那儿
mysql> insert student(name,age) values('wangwu',98);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted


2. 主数据库上全备之后再将文件发送给从数据库
[root@master ~]# mysqldump -uroot -predhat --all-databases > all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@master ~]# ls
all.sql  anaconda-ks.cfg  mysql
[root@master ~]# scp all.sql 192.168.50.135:~
The authenticity of host '192.168.50.135 (192.168.50.135)' can't be established.
ECDSA key fingerprint is SHA256:sQqUajvZSfuHD9T+PkdNt5QwjMJ5RNCws+LTyZAcD54.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.50.135' (ECDSA) to the list of known hosts.
root@192.168.50.135's password: 
all.sql                         100%  841KB  46.6MB/s   00:00   


3. 在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@slave ~]# ls
all.sql  anaconda-ks.cfg  mysql
[root@slave ~]# mysql -uroot -predhat < all.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@slave ~]# mysql -uroot -predhat -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
[root@slave ~]# mysql -uroot -predhat -e 'select * from school.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | tom      |   29 |
|  2 | jerry    |   24 |
|  3 | zhangsan |   18 |
|  4 | lisi     |   56 |
+----+----------+------+

3.3.3 解锁，并在主数据库里创建一个同步账号授权给从数据库使用

1. 退出即解锁
mysql> quit
Bye


2. 创建一个用户使其能在192.168.50.135登陆
mysql> create user repl@192.168.50.135 identified by 'repl123!';
Query OK, 0 rows affected (4.88 sec)


3. 授权
 mysql> grant replication slave on *.* to 'repl'@'192.168.50.135';
Query OK, 0 rows affected (0.00 sec)

4. 刷新
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

3.3.4 配置主数据库

在配置文件里添加最后两行
[root@master mysql]# vim /etc/my.cnf 

[mysqld]
basedir = /usr/local/mysql
datadir = /data/3306
socket = /tmp/mysql.sock
port = 3306
pid-file = /data/3306/mysql.pid
user = mysql
skip-name-resolve



log-bin = mysql_bin
server-id = 10


重启服务
[root@master ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 


查看主库状态
[root@master ~]# mysql -uroot -predhat
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 2
Server version: 5.7.31-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.3.5 配置从数据库

在配置文件里添加最后两行
[root@slave ~]# vim /etc/my.cnf 

[mysqld]
basedir = /usr/local/mysql
datadir = /data/3306
socket = /tmp/mysql.sock
port = 3306
pid-file = /data/3306/mysql.pid
user = mysql
skip-name-resolve



relay-log = myrelay
server-id = 20


重启服务
[root@slave ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 


配置并启动主从配置
[root@slave ~]# mysql -uroot -predhat
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 2
Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> 
mysql> change master to master_host='192.168.50.131',master_user='repl',master_password='repl123!',master_log_file='mysql_bin.000001',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)



查看服务器状态
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.50.131
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: myrelay.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes     这里是两个yes,把这里改成no就是将其停止,用stop slave
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 519
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10
                  Master_UUID: ba65ed8e-19c1-11eb-b7dd-000c291636de
             Master_Info_File: /data/3306/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

3.3.6 测试验证

查看从数据库上的数据是否同步过来了

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use school;
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> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)

mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | tom      |   29 |
|  2 | jerry    |   24 |
|  3 | zhangsan |   18 |
|  4 | lisi     |   56 |
+----+----------+------+
4 rows in set (0.00 sec)

在主库里往表里插入qianliu

mysql> insert school.student(name,age) values('qianliu',30);
Query OK, 1 row affected (0.01 sec)

mysql> select * from school.student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | tom      |   29 |
|  2 | jerry    |   24 |
|  3 | zhangsan |   18 |
|  4 | lisi     |   56 |
|  5 | qianliu  |   30 |
+----+----------+------+
5 rows in set (0.00 sec)

查看从库是否有qianliu

mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | tom      |   29 |
|  2 | jerry    |   24 |
|  3 | zhangsan |   18 |
|  4 | lisi     |   56 |
|  5 | qianliu  |   30 |
+----+----------+------+
5 rows in set (0.00 sec)


查看进程列表
mysql> show processlist\G
*************************** 1. row ***************************
     Id: 2
   User: root
   Host: localhost
     db: school
Command: Query
   Time: 0
  State: starting
   Info: show processlist
*************************** 2. row ***************************
     Id: 3
   User: system user               系统账户
   Host: 
     db: NULL
Command: Connect                  等待连接
   Time: 1072
  State: Waiting for master to send event             等待连接master
   Info: NULL
*************************** 3. row ***************************
     Id: 4
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 543
  State: Slave has read all relay log; waiting for more updates                   slave已经读取了日志，等待更新
   Info: NULL
3 rows in set (0.00 sec)


杀掉进程（此处杀掉了id=4的进程）
mysql> kill 4;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 2
   User: root
   Host: localhost
     db: school
Command: Query
   Time: 0
  State: starting
   Info: show processlist
*************************** 2. row ***************************
     Id: 3
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 1523
  State: Waiting for master to send event
   Info: NULL
2 rows in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.50.131
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 423
               Relay_Log_File: myrelay.000002
                Relay_Log_Pos: 589
        Relay_Master_Log_File: mysql_bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: No                 杀掉了该进程，此处就变成no。若要启动，则执行start slave
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 423
              Relay_Log_Space: 788
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10
                  Master_UUID: ba65ed8e-19c1-11eb-b7dd-000c291636de
             Master_Info_File: /data/3306/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)


再次启动slave则slave就变成yes
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.50.131
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 423
               Relay_Log_File: myrelay.000002
                Relay_Log_Pos: 589
        Relay_Master_Log_File: mysql_bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes         此处已变为yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 423
              Relay_Log_Space: 788
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10
                  Master_UUID: ba65ed8e-19c1-11eb-b7dd-000c291636de
             Master_Info_File: /data/3306/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)