首页 > 代码库 > mysql主主互备模式配置
mysql主主互备模式配置
本文为南非蚂蚁的书籍《循序渐进linux-第二版》-8.3.5的读笔记
mysql双主互备架构图
mysql主主互备模式配置
环境：
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
----------------------------------------
centos6.x安装mysql
# yum -y install mysql mysql-server
centos7.x安装mariaDB
# yum -y install mariadb-server mariadb
安装完成后目录结构如下：
启动mysql
# /etc/init.d/mysqld start
创建mysql密码：(jzh0024)
# mysql_secure_installation
/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we‘ll need the current
password for the root user. If you‘ve just installed MariaDB, and
you haven‘t set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
#这里输入目前mariadb数据库的root密码，默认是空
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] y
#这里询问是否设置mariadb数据库root的密码，输入"y"给用户设置一个新的密码
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y #这里询问是否删除匿名用户，输入"y"删除
... Success!
Normally, root should only be allowed to connect from ‘localhost‘. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] y #这里询问是否关闭root用户远程登录权限，输入"y"
... Success!
By default, MariaDB comes with a database named ‘test‘ that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y #这里询问是否删除测试数据库及其权限，输入"y"
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y #这里询问是否重新载入授权表，输入"y"
... Success!
Cleaning up...
All done! If you‘ve completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
[root@localhost ~]#
至此，mysql数据库安装完成。
-----------------------------------------
1.修改mysql配置文件
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.%
这里需要注意的是，不要在主库上使用binlog-do-db或binlog-ignore-db选项，也不要在从库上使用replication-db-do或replication-db选项，因为这样可能产生跨库更新失败的问题；
推荐从库上使用replicate_wild_do_table和replicate-wild-ignore-table两个选项来解决复制过滤问题
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;
+------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| member_no | char(9) | NO | PRI | NULL | |
| name | char(5) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| exam_score | tinyint(4) | YES | | NULL | |
+------------+------------+------+-----+---------+-------+
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;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| 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)
ERROR:
No query specified
至此，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;
+-----------+-------+------------+------------+
| member_no | name | birthday | exam_score |
+-----------+-------+------------+------------+
| 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到DB1的主从复制
DB2数据库中创建复制用户
mysql> grant replication slave on *.* to ‘repl_user1‘@‘10.24.24.111‘ identified by ‘repl_password1‘;
刷新授权表
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.000003 | 273 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
在DB1的数据库中将DB2设为自己的主服务器
mysql> change master to \
-> master_host=‘10.24.24.112‘,
-> master_user=‘repl_user1‘,
-> master_password=‘repl_password1‘,
-> master_log_file=‘mysql-bin.000003‘,
-> master_log_pos=273;
在DB1上启动从服务器
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
查看DB1上从服务器的运行状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.24.24.112
Master_User: repl_user1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 273
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000003
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: 273
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)
ERROR:
No query specified
Slave_IO_Running和Slave_SQL_Running都处于YES状态。表明DB1上复制服务运行正常，mysql双主模式主从复制配置完毕。
------------------------------------
验证数据的完整性
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;
+---------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------+------+-----+---------+-------+
| member_no | char(9) | NO | PRI | NULL | |
| name001 | char(5) | YES | | NULL | |
| birthday001 | date | YES | | NULL | |
| exam_score001 | tinyint(4) | YES | | NULL | |
+---------------+------------+------+-----+---------+-------+
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;
+-----------+---------+-------------+---------------+
| member_no | name001 | birthday001 | exam_score001 |
+-----------+---------+-------------+---------------+
| 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;
+-----------+---------+-------------+---------------+
| member_no | name001 | birthday001 | exam_score001 |
+-----------+---------+-------------+---------------+
| 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;
+-----------+-------+------------+------------+
| member_no | name | birthday | exam_score |
+-----------+-------+------------+------------+
| 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)
删除很快,基本上是实时同步的.
========================================================
本文出自 “Linux运维的自我修养” 博客，请务必保留此出处http://ywliyq.blog.51cto.com/11433965/1856963
mysql主主互备模式配置