MySQL主主

准备

1. 安装好mysql
2. 关闭防火墙和沙盒
3. A数据库ID：192.168.2.55
4. B数据库ID：192.168.2.88

A数据库(主)-> B数据库(从)

编辑A数据库(主)

1.编辑配置文件

vim /etc/my.cnf

# binary logging is required for replication
log-bin=mysql-bin
auto-increment-increment=10
auto-increment-offset=1
binlog-do-db=kgcdb
replicate-do-db=kgcdb
binlog-ignore-db=mysql
log-slave-updates=on

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1

2.重启mysql服务

service mysqld restart

3.进入mysql

mysql -uroot -p

未设置密码

4.创建数据库kgcdb

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

5.给备份用户授权，允许slave从B服务器登录到主服务器

自定义备份用户名（use）和密码（123.com）：

mysql> grant replication slave on *.* to 'use'@'192.168.2.%' identified by '123.com';
Query OK, 0 rows affected (0.00 sec)

6.刷新系统授权并退出

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

mysql> quit
Bye

7.备份数据库kgcdb

mysqldump -u root -p kgcdb > ~/kgcdb.sql
Enter password: 

密码为数据库密码

8.将备份的数据库发送到终端192.168.2.88

scp ~/kgcdb.sql root@192.168.2.88:~/kgcdb.sql
root@192.168.2.88's password: 

密码为192.168.2.88root密码

9.再次登录并显示master状态

mysql -uroot -p

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 |      197 | kgcdb        | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

编辑B数据库(从)

1.编辑配置文件

vim /etc/my.cnf

# binary logging is required for replication
log-bin=mysql-bin
auto-increment-increment=10
auto-increment-offset=2
replicate-do-db=kgcdb
log-slave-update=true
binlog-do-db=kgcdb
log_slave_updates=on


# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 2

2.重启mysql服务

service mysqld restart

3.查看kgcdb.sql文件

ls ~/kgcdb.sql 

4.进入mysql

mysql -uroot -p123456

已设置密码123456

5.创建数据库kgcdb（多source几次）

mysql> create database kgcdb;
Query OK, 1 rows affected (0.00 sec)

mysql> source /root/kgcdb.sql
mysql> source /root/kgcdb.sql
mysql> source /root/kgcdb.sql

6.从数据库A获得授权

若已经授权，可重置slave参数，重新授权。

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

mysql> change master to master_host='192.168.2.55',master_user='use',master_password='123.com',master_log_file='mysql-bin.000005',master_log_pos=197;
Query OK, 0 rows affected (0.15 sec)

7.启动slave，并查看slave状态

mysql> slave start;
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.2.55
                  Master_User: use
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 197
               Relay_Log_File: cai6-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: kgcdb
          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: 197
              Relay_Log_Space: 408
              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: 1
1 row in set (0.00 sec)

B数据库(主)-> A数据库(从)

编辑B数据库(主)

1.进入mysql

mysql -uroot -p123456

2.给备份用户授权，允许slave从A服务器登录到主服务器

自定义备份用户名（use123）和密码（123.com）：

mysql> grant replication slave on *.* to 'use123'@'192.168.2.55' identified by '123.com';
Query OK, 0 rows affected (0.00 sec)

3.刷新系统授权并显示master状态信息

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

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000016 |      107 | kgcdb        |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

编辑A数据库(从)

1.进入mysql

mysql -uroot -p123456

2.从数据库B获得授权

若已经开启slave，则先停用slave,再授权。

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

mysql> change master to master_host='192.168.2.88',master_user='use123',master_password='123.com',master_log_file='mysql-bin.000016',master_log_pos=107;
Query OK, 0 rows affected (0.15 sec)

7.启动slave，并查看slave状态

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.2.88
                  Master_User: use123
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000016
          Read_Master_Log_Pos: 107
               Relay_Log_File: cai4-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000016
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: kgcdb
          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: 107
              Relay_Log_Space: 408
              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: 2
1 row in set (0.00 sec)

验证

1.在B服务器192.168.2.88上，进入kgcdb数据库，创建 exp1表

mysql> use kgcdb
Database changed
mysql> create table exp1( id int(11), name varchar(20) );                             
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-----------------+
| Tables_in_kgcdb |
+-----------------+
| exp1            |
+-----------------+
2 rows in set (0.00 sec)

2.在A服务器192.168.2.55上，进入kgcdb数据库，查看到test文件，并插入类容到test文件

mysql> use kgcdb
Database changed
mysql> show tables;
+-----------------+
| Tables_in_kgcdb |
+-----------------+
| exp1            |
+-----------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO exp1 VALUES(12,'CAI');
Query OK, 1 row affected (0.00 sec)

3.返回B查看插入内容

mysql> select * from  exp1;
+------+------+
| id   | name |
+------+------+
|   12 | CAI  |
+------+------+
1 row in set (0.00 sec)

成功！