|NO.Z.00094|——————————|^^ 部署 ^^|——|Linux&MySQL集群.V02|——|主主备份|

一、mysql主主备份

### --- mysql主主备份

~~~     以 1 为主，2 为从配置一遍主从
~~~     在主配置文件中配置一下（开启二进制日志和其他内容）

### --- 部署规划

~~~     server21:20.20.20.21    主服务器1
~~~     server22:20.20.20.22    主服务器2

二、部署主服务器1

### --- 部署主服务器1:

[root@server21 ~]# service mysqld stop
[root@server21 ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=mysql-bin                                   // 开启二进制日志
server-id=21                                        // 开启二进制日志  
#replicate-do-db=test                               // 提高效率，将不必要的资源不写入二进制文件
binlog-ignore-db=mysql                              // 忽略掉mysqld的同步
#binlog-ignore-db=information_schema
#auto-increment-increment=2                         // 防止主键冲突，数据的ID号从1开始，每次+2即1,3,5,7......
#auto-increment-offset=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

### --- 启动主服务器1

[root@server21 ~]# service mysqld start

三、部署主服务器2：在主服务器2上做相同的配置

### --- 部署主服务器:2:

[root@server22 ~]# service mysqld stop
[root@server22 ~]# vim /etc/my.cnf 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=mysql-bin
server-id=22
#replicate-do-db=test
binlog-ignore-db=mysql
##binlog-ignore-db=information_schema
##auto-increment-increment=2
##auto-increment-offset=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

### --- 启动主服务器2

[root@server22 ~]# service mysqld start

四、1 为主 2 为从：

### --- 启动服务器

[root@server21 ~]# service mysqld start
[root@server22 ~]# service mysqld start

### --- 在主服务器(1)上授权

[root@server22 ~]# mysql
mysql> grant replication slave on *.* to slave@'20.20.20.21' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      257 |              | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

### --- 在从服务器(2)上保存授权信息

[root@server21 ~]# mysql 
mysql> change master to master_user='slave',master_password='123456',master_host='20.20.20.22',master_log_file='mysql-bin.000004',master_log_pos=257;
Query OK, 0 rows affected (0.05 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: 20.20.20.22
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 257
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 257
              Relay_Log_Space: 407

五、验证测试

### --- 主服务器1上测试

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

### --- 主服务器1上创建abc数据库

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

### --- 主服务器2上查看是否创建成功

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| abc                |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

### --- 主服务器2上删除abc数据库

mysql> drop database abc;
Query OK, 0 rows affected (0.05 sec)

### --- 主服务器上查看是否删除abc成功

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

---

 

 

 

 

 

 

 

 

 

---

Walter Savage Landor:strove with none,for none was worth my strife.Nature I loved and, next to Nature, Art:I warm'd both hands before the fire of life.It sinks, and I am ready to depart

                                                                                                                                                   ——W.S.Landor

---