mysql主主互備模式配置_command_02

環境：

DB1：主伺服器 centos6.6 mysql5.1.73

IP:10.24.24.111

DB2：從伺服器 centos6.6 mysql5.1.73

IP:10.24.24.112

mysql VIP: 10.24.24.112

DB1 /etc/my.cnf配置,[mysqld]段新增：

server-id = 1

log-bin=mysql-bin

replay-log = mysql-relay-bin

replicate-wild-ignore-table=mysql.%

replicate-wild-ignore-table=test.%

replicate-wild-ignore-table=information_schema.%

DB /etc/my.cnf配置， [mysqld]段新增：

server-id = 2

log-bin=mysql-bin

relay-log = mysql-relay-bin

replicate-wild-ignore-table=mysql.%

replicate-wild-ignore-table=test.%

replicate-wild-ignore-table=information_schema.%

2.手動配置資料庫

DB1先建立一個資料庫及表，用於同步測試

mysql> create database ywadmin;

mysql> use ywadmin;

建立表

mysql> create table personal(member_no char(9) not null,name char(5),birthday date,exam_score tinyint,primary key(member_no));

檢視錶內容

mysql> desc personal;

+------------+------------+------+-----+---------+-------+

+------------+------------+------+-----+---------+-------+

+------------+------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

DB1進行鎖表並備份資料庫

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

不要退出終端,否則鎖表失敗;新開啟一個終端對資料進行備份,或者使用mysqldump進行備份

# cd /var/lib/

# tar zcvf mysql.tar.gz mysql

# scp -P50024 mysql.tar.gz root@10.24.24.112:/var/lib/

root@10.24.24.112's password:

mysql.tar.gz 100% 213KB 213.0KB/s 00:00

注意:此處需要開啟DB2授權root遠端登入

# vim /etc/ssh/sshd_config

#PermitRootLogin no

資料傳輸到DB2後,依次重啟DB1,DB2的資料庫

[root@DB1 ~]# /etc/init.d/mysqld restart

Stopping mysqld: [ OK ]

Starting mysqld: [ OK ]

[root@DB2 ~]# /etc/init.d/mysqld restart

Stopping mysqld: [ OK ]

Starting mysqld: [ OK ]

3.建立複製使用者並授權

DB1上建立複製使用者,

mysql> grant replication slave on *.* to 'repl_user'@'10.24.24.112' identified by 'repl_password';

Query OK, 0 rows affected (0.00 sec)

重新整理授權表

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> show master status;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000002 | 271 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

然後在DB2的資料庫中將DB1設為自己的主伺服器

# cd /var/lib/

# tar xf mysql.tar.gz

mysql> change master to \

-> master_host='10.24.24.111',

-> master_user='repl_user',

-> master_password='repl_password',

-> master_log_file='mysql-bin.000002',

-> master_log_pos=271;

需要注意master_log_file和 master_log_pos選項，這兩個值是剛才在DB1上查詢到的結果

DB2上啟動從伺服器，並檢視DB2上的從伺服器執行狀態

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: 10.24.24.111

Master_User: repl_user

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 271

Relay_Log_File: mysql-relay-bin.000002

Relay_Log_Pos: 251

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table: mysql.%,test.%,information_schema.%

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 271

Relay_Log_Space: 406

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:

1 row in set (0.00 sec)

至此，DB1到DB2的MYSQL主從複製已完成。

DB1上插入資料

mysql> use ywadmin;

mysql> show tables;

+-------------------+

| Tables_in_ywadmin |

+-------------------+

| personal |

+-------------------+

1 row in set (0.00 sec)

mysql> insert into personal values ('001','netseek','1983-03-15','95');

mysql> insert into personal values ('002','heihei','1982-02-24','90');

mysql> insert into personal values ('003','gogo','1985-05-21','85');

mysql> insert into personal values ('004','haha','1984-02-25','84');

mysql> insert into personal values ('005','linlin','1982-04-28','85');

mysql> insert into personal values ('006','xinxin','1985-03-15','75');

mysql> desc personal;

DB2資料庫上驗證資料是否同步

mysql> use ywadmin;

mysql> select * from personal;

+-----------+-------+------------+------------+

+-----------+-------+------------+------------+

| 001 | netse | 1983-03-15 | 95 |

| 002 | heihe | 1982-02-24 | 90 |

| 003 | gogo | 1985-05-21 | 85 |

| 004 | haha | 1984-02-25 | 84 |

| 005 | linli | 1982-04-28 | 85 |

| 006 | xinxi | 1985-03-15 | 75 |

+-----------+-------+------------+------------+

6 rows in set (0.00 sec)

資料已完成複製.

---------------------------------------------

驗證資料的完整性

DB2上建立新資料庫、表

mysql> create database ywadmin001;

mysql> use ywadmin001;

建立表

mysql> create table personal001(member_no char(9) not null,name001 char(5),birthday001 date,exam_score001 tinyint,primary key(member_no));

檢視錶內容

mysql> desc personal001;

+---------------+------------+------+-----+---------+-------+

+---------------+------------+------+-----+---------+-------+

+---------------+------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

vmysql> use ywadmin001;

mysql> insert into personal001 values ('001','netseek','1983-03-15','95');

mysql> insert into personal001 values ('002','heihei','1982-02-24','90');

mysql> insert into personal001 values ('003','gogo','1985-05-21','85');

mysql> select * from personal001;

+-----------+---------+-------------+---------------+

+-----------+---------+-------------+---------------+

| 001 | netse | 1983-03-15 | 95 |

| 002 | heihe | 1982-02-24 | 90 |

| 003 | gogo | 1985-05-21 | 85 |

+-----------+---------+-------------+---------------+

3 rows in set (0.00 sec)

並在 personal表中插入資料

mysql> use ywadmin;

mysql> show tables;

mysql> insert into personal values ('007','ywadmin','1987-11-07','100');

mysql> insert into personal values ('008','ywliyq','1986-12-25','99');

mysql> insert into personal values ('009','xiaxia','1990-12-27','97');

DB1資料庫上驗證資料是否同步

新的資料庫及表是否被建立

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| ywadmin |

| ywadmin001 |

+--------------------+

4 rows in set (0.00 sec)

mysql> use ywadmin001;

mysql> show tables;

+----------------------+

| Tables_in_ywadmin001 |

+----------------------+

| personal001 |

+----------------------+

1 row in set (0.00 sec)

mysql> select * from personal001;

+-----------+---------+-------------+---------------+

+-----------+---------+-------------+---------------+

| 001 | netse | 1983-03-15 | 95 |

| 002 | heihe | 1982-02-24 | 90 |

| 003 | gogo | 1985-05-21 | 85 |

+-----------+---------+-------------+---------------+

3 rows in set (0.00 sec)

新建庫、表中的內容已同步。

原表插入的資料是否同步

mysql> use ywadmin;

mysql> select * from personal;

+-----------+-------+------------+------------+

+-----------+-------+------------+------------+

| 001 | netse | 1983-03-15 | 95 |

| 002 | heihe | 1982-02-24 | 90 |

| 003 | gogo | 1985-05-21 | 85 |

| 004 | haha | 1984-02-25 | 84 |

| 005 | linli | 1982-04-28 | 85 |

| 006 | xinxi | 1985-03-15 | 75 |

| 007 | ywadm | 1987-11-07 | 100 |

| 008 | ywliy | 1986-12-25 | 99 |

| 009 | xiaxi | 1990-12-27 | 97 |

+-----------+-------+------------+------------+

9 rows in set (0.00 sec)

原表插入的列也已同步, 資料已完成複製.

刪除DB2上的庫

mysql> drop database ywadmin001;

DB1上檢查 ywadmin001庫是否被刪除

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| ywadmin |

+--------------------+

3 rows in set (0.00 sec)

刪除很快,基本上是實時同步的.

參考博文：https://blog.51cto.com/ywliyq/1856963

MySQL 主備

相關文章