Mysql备份主从、主主、多主一从

实验环境mysql5.7

1. MySQL 主从备份

主服务器IP：192.168.0.24

从（主）服务器IP：192.168.0.50

从服务器IP：192.168.0.184

前提条件：安装了 mysql,开启了二进制日志，主从服务器进行mysql开机启动（chkconfig mysqld on），并写重新启动mysqld服务后

主从服务器在配置文件中开启二进制日志

vim /etc/my.cnf

[mysqld]
#添加以下命令到文件中
log-bin=mysql-bin
server-id=24
#id建议用ip地址末端位数，主从服务器ip不能一样。

主服务器开启授权：

mysql> grant replication slave on *.* to gaoda@'%' identified by '123456';
#gaoda@后面‘%’代表不限ip地址，如果有需要可以设置相应ip字段。

然后在主服务器上授权，从服务器保存授权的信息，之后在从服务器会产生授权信息文件

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      826 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

从服务器保存授权信息

mysql>change master to master_user='gaoda',master_password='123456',master_host='192.168.0.24',master_log_file='mysql-bin.000003',master_log_pos=826;

mysql> start slave;      #开启从服务器
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.0.24
                  Master_User: gaoda
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 826
               Relay_Log_File: Centos7-02-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes        #线程已启动  
            Slave_SQL_Running: Yes        #线程已启动

测试： 主服务器

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aa                 |
| bb                 |
| gaoda              |
| mysql              |
| performance_schema |
| sys                |
| web                |
+--------------------+
8 rows in set (0.00 sec)

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

mysql> use cc
Database changed
mysql> show tables;
Empty set (0.00 sec)
#在从服务上创建指发现主服务接收不到。

测试：从服务器

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

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

mysql> use cc
Database changed
mysql> create table a1(id int,name char(30));
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+--------------+
| Tables_in_cc |
+--------------+
| a1           |
+--------------+
1 row in set (0.00 sec)

2、主主备份

在主从服务器修改配置文件，

[mysqld]
log-bin=mysql-bin
server-id=2        
#注意主从服务器ID 号码不能 一致
replicate-do-db=test
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
auto-increment-increment=2
auto_increment_offset=2
#防止主键冲突，数据从ID号2开始，每次+2。主服务设置为1

重启主从服务器

1为主2为从

在主服务器上授权

mysql> grant replication slave on *.* to  zhangsan@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000001 |      446 |              | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)

从服务器接收授权信息

mysql> change master to master_user='zhangsan',master_password='123456',master_host='192.168.0.41',master_log_file='mysql-bin.000001',master_log_pos=446;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

2为主1为从

在从服务上授权

mysql> grant replication slave on *.* to lisi@'%' identified by 'Fwd@715030';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000001 |      442 |              | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)

主服务器接收

mysql> change master to master_user='lisi',master_password='Fwd@715030',master_host='192.168.0.50',master_log_file='mysql-bin.000001',master_log_pos=442;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

在主从服务器相互执行 start slave（互为主从），并且查看从服务器内容，

mysql> show slave status\G;
*************************** 1. row ***************************
               
             Slave_IO_Running: Yes    #线程已启用
            Slave_SQL_Running: Yes