淘宝数以万计的用户重要数据是如何实现不丢失的？---MySQL主从复制+MySQL主主复制

1.MYSQL主从复制

1.1用途：

Mysql主从复制的目的是实现数据冗余备份，从而保障服务器压力不断增加时一旦出现主库宕机可以快速将web应用数据库配置切换到slave数据库，以确保web应用较高的可用率。

1.2工作原理：

1. Slave上执行slave start，Slave IO线程会通过在Master创建的授权用户连接上至Master，并请求master从指定的文件和位置之后发送bin-log日志内容；
2. Master接收到来自slave IO线程的请求后，master IO线程根据slave发送的指定bin-log日志position点之后的内容，然后返回给slave的IO线程。
3. 返回的信息中除了bin-log日志内容外，还有master最新的binlog文件名以及在binlog中的下一个指定更新position点；
4. Slave IO线程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和position点记录到master.info文件中，以便在下一次读取的时候能告知master从响应的bin-log文件名及最后一个position点开始发起请求；
5. Slave Sql线程检测到relay-log中内容有更新，会立刻解析relay-log的内容成在Master真实执行时候的那些可执行的SQL语句，将解析的SQL语句并在Slave里执行，执行成功后，Master库与Slave库保持数据一致。

1.3实施注意事项:

1.需要准备两台以上服务器，可以配置一主多从，多主多从；

2.必须确保slave库的position点与master库的同步开始点位一致，文件名一致

1.4主从结构：

1.5实施过程:

1.准备两台服务器(centos7.4)，都安装好MySQL数据库

主库：

IP：192.168.1.112 版本：MySQL8.0.31

从库：

IP: 192.168.1.123 版本：MySQL8.0.31

wget https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm   ###下载镜像包
rpm -ivh mysql80-community-release-el7-1.noarch.rpm    ###获取安装包
sed -i 's#gpgcheck=1#gpgcheck=0#g' /etc/yum.repos.d/mysql-community.repo   ###避免版本问题密钥报错
yum install mysql-community-server -y				###yum安装数据库


systemctl enable mysqld				###设置开机自启
systemctl start mysqld					###启动MySQL
grep 'password' /var/log/mysqld.log 				###获取初始密码
2022-12-05T09:59:08.779856Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ++V5oq:aurX>
[root@zzp112 ~]# mysql -uroot -p'++V5oq:aurX>'																	####登录MySQL
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.31

Copyright (c) 2000, 2022, 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> alter user 'root'@'localhost' identified by 'P@ssw0rd';									###设置新密码
Query OK, 0 rows affected (0.01 sec)

mysql> set global validate_password.policy=0;								###设置密码强度
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password.length=1;							###设置密码长度
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';				###设置密码
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;						###刷新

2.配置主库 /etc/my.cnf配置文件[mysqld]部分

[root@zzp112 ~]# vim /etc/my.cnf

配置内容：添加以下

[mysqld]
server-id = 1
log-bin = mysql-bin

3.Master数据库服务器命令行中 创建tongbu用户及密码并设置权限，执行如下命令，查看bin-log文件及position点

[root@zzp112 ~]# mysql -uroot -p123456				###登录
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 9
Server version: 8.0.31 MySQL Community Server - GPL

Copyright (c) 2000, 2022, 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> create user 'tongbu'@'%' identified with mysql_native_password by 'P@ssw0rd';    ####创建同步用户
Query OK, 0 rows affected (0.04 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'tongbu'@'%';						####设置tongbu用户权限
Query OK, 0 rows affected (0.01 sec)

mysql> show  master  status;								####查看起始点位
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     1305 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> 

4.slave数据库，配置/etc/my.cnf，则需修改server-id，MASTER与Slave端server-id不能一样，Slave端也无需开启bin-log功能

添加内容：

[mysqld]
server-id = 2

5.Slave指定Master IP、用户名、密码、bin-log文件名（ mysql-bin.000002）及position（1305）

[root@zzp123 ~]# mysql -uroot -p123456
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 9
Server version: 8.0.31 MySQL Community Server - GPL

Copyright (c) 2000, 2022, 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='192.168.10.110',master_user='tongbu',master_password='P@ssw0rd',master_log_file='mysql--bin.000001',master_log_pos=664;
Query OK, 0 rows affected, 8 warnings (0.04 sec)             ###指定Master IP、用户名、密码、bin-log文件名（ mysql-bin.000002）及position（1305）

6.在slave启动slave start，并执行show slave status\G查看Mysql主从状态：

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)

> show slave status\G							###查看主从状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.1.112
                  Master_User: tongbu
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1305
               Relay_Log_File: zzp123-relay-bin.000002
                Relay_Log_Pos: 326
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes													####I/O线程已启动
            Slave_SQL_Running: Yes															####SQL线程已启动
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 

7.在主库创建数据库查看从库是否同步：

2.MySQL主主复制

1.在原来主从基础上修改slave库 /etc/my.cnf文件

修改内容：

[mysqld]
server-id = 2
log-bin = mysql-bin1

2.Master123数据库服务器命令行中 创建tongbu用户及密码并设置权限，执行如下命令，查看bin-log文件及position点

[root@zzp123 ~]# mysql -uroot -p123456
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 16
Server version: 8.0.31 MySQL Community Server - GPL

Copyright (c) 2000, 2022, 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> create user 'tongbu2'@'%' identified with mysql_native_password by 'P@ssw0rd';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'tongbu'@'%';
ERROR 1410 (42000): You are not allowed to create a user with GRANT
mysql> GRANT REPLICATION SLAVE ON *.* TO 'tongbu2'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |     1512 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

3.Slave112指定Master IP、用户名、密码、bin-log文件名（ binlog.000001 ）及position（1512）

mysql> change master to 
    -> master_host='192.168.1.123',master_user='tongbu2',master_password='P@ssw0rd',master_log_file='binloog.000001
Query OK, 0 rows affected, 8 warnings (0.03 sec)

mysql> slave start;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'slave start' at line 1
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.1.123
                  Master_User: tongbu2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 1512
               Relay_Log_File: zzp112-relay-bin.000002
                Relay_Log_Pos: 323
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 

4.测试：123库创建数据库，112库可以查询到

112库创建表插入数据，123库也可以查询到