MYSQL AB复制
环境配置：RHEL 6.5 mysql-5.1.71-1.el6.x86_64
Master:172.25.44.44
Slave:172.25.44.55
MySQL 支持单向、异步复制,复制过程中一个服务器充当主服务器,而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件,并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时,它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。 从服务器接收从那时起发生的任何更新,然后封锁并等待主服务器通知新的更新。请注意当你进行复制时,所有对复制中的表的更新必须在主服务器上进行。否则必须要小心,以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。
单向复制有利于健壮性、速度和系统管理:
1.主服务器/从服务器设置增加了健壮性。主服务器出现问题时,你可以切换到从服务器作为备份。
2.通过在主服务器和从服务器之间切分处理客户查询的负荷,可以得到更好的客户响应时间。SELECT查询可以发送到从服务器以降低主服务器的查询处理负荷。但 修改数据的语句仍然应发送到主服务器,以便主服务器和从服务器保持同步。如果非更新查询为主,该负载均衡策略很有效,但一般是更新查询。
3.使用复制的另一个好处是可以使用一个从服务器执行备份,而不会干扰主服务器。在备份过程中主服务器可以继续处理更新。
MySQL 提供了数据库的同步功能,这对我们实现数据库的冗灾、备份、恢复、负载均衡等都是有极大帮助。
Part 1 MYSQL单向复制
在Master,Slave上：
yum install -y mysql-server ##mysql数据库的版本,两个数据库版本要相同,或slave比master 版本高 /etc/init.d/mysqld start ##启动数据库 mysql_secure_installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MySQL to secure it, we'll need the current password for the root user. If you've just installed MySQL, 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): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MySQL root user without the proper authorisation. Set root password? [Y/n] y New password: jeffrey Re-enter new password: jeffrey Password updated successfully! Reloading privilege tables.. ... Success! By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL 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 ... 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 ... Success! By default, MySQL 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 - 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 ... Success! Cleaning up... All done! If you've completed all of the above steps, your MySQL installation should now be secure. Thanks for using MySQL! [root@server4 ~]# mysql -pjeffrey ##用设置的新root密码登陆数据库 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.1.71 Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | +--------------------+ 2 rows in set (0.00 sec) mysql> quit Bye 在master节点上： vim /etc/my.cnf server-id=1 log-bin=mysql-bin ##启动二进制日志系统 /etc/init.d/mysqld restart ##重启数据库 mysqlbinlog /var/lib/mysql/mysql-bin.000001 ##查看数据库日志 如图：
[root@server4 mysql]# file mysql-bin.000001
mysql-bin.000001: MySQL replication log ##该文件为用于MYSQL复制的日志文件
[root@server4 mysql]# mysql -pjeffrey
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.71-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant replication slave on *.* to gf@'172.25.44.55' identified by 'jeffrey'; ##设置复制节点为172.25.44.55的slave节点，将权限赋给新建用户gf，密码设为jeffrey
Query OK, 0 rows affected (0.00 sec)
mysql> show master status; ##查看主节点的状态，记录下Position和File的值
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 256 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> create database GF; ##建立新的数据库GF
Query OK, 1 row affected (0.00 sec)
mysql> use GF ##使用新建的数据库
Database changed
mysql> create table usertb (
-> username varchar(25) not null,
-> password varchar(25) not null);
Query OK, 0 rows affected (0.08 sec) ##建立新的数据表usertb，用户名（非空），密码（非空）
mysql> show tables;
+--------------+
| Tables_in_GF |
+--------------+
| usertb |
+--------------+
1 row in set (0.00 sec)
mysql> desc usertb; ##查看数据表usertb
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(25) | NO | | NULL | |
| password | varchar(25) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into usertb value ('user1','111'); ##插入数据user1，密码为111
Query OK, 1 row affected (0.00 sec)
mysql> insert into usertb value ('user2','222'); ##插入数据user2，密码为222
Query OK, 1 row affected (0.00 sec)
在slave节点：
vim /etc/my.cnf ##修改mysql主配置文件 server-id=2 /etc/init.d/mysqld restart ##重启数据库 [root@server5 ~]# mysql -h 172.25.44.44 -ugf -pjeffrey ##使用新建的用户和密码登陆master的数据库 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.1.71-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> quit Bye [root@server5 mysql]# mysql -pjeffrey Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.1.71 Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> change master to master_host='172.25.44.44',master_user='gf',master_password='jeffrey',master_log_file='mysql-bin.000001',master_log_pos=256; ##设置该slave的master为'172.25.44.44'，用户名为gf',密码为jeffrey，master的日志文件为mysql-bin.000001，Position为256 Query OK, 0 rows affected (0.34 sec) mysql> start slave; ##开启slave Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G; ##查看slave节点状态，Slave_IO_Running: Yes，Slave_SQL_Running: Yes，如果都是yes ,表示从库的I/O,Slave_SQL线程都正确开启.表明数据库正在同步 如图：
mysql> show databases; ##查看得到master上建立的GF库，说明成功
+--------------------+
| Database |
+--------------------+
| information_schema |
| GF |
| mysql |
+--------------------+
3 rows in set (0.00 sec)
mysql> use GF
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from usertb; ##查看得到master上GF库里建立的user1,user2及其密码，说明成功
+----------+----------+
| username | password |
+----------+----------+
| user1 | 111 |
| user2 | 222 |
+----------+----------+
2 rows in set (0.00 sec)
在master节点：
mysql> delete from usertb where username='user1'; ##删除usertb里的user1
Query OK, 1 row affected (0.00 sec)
mysql> update usertb set password=password('222'); ##更改usertb里的密码为222的用户的密码，并改为非明文的密码形式
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
在slave节点：
mysql> select * from usertb; +----------+---------------------------+ | username | password | +----------+---------------------------+ | user2 | *531E182E2F72080AB0740FE2 | +----------+---------------------------+ 1 row in set (0.00 sec) mysql> quit Bye 说明MYSQL的单向复制成功。
Part 2 MYSQL双向复制
在Master,Slave上：
rpm -e `rpm -qa |grep mysql` --nodeps ##移除掉mysql的相关组件 rm -rf /var/lib/mysql/ yum install -y mysql-community-client-5.7.11-1.el6.x86_64.rpm mysql-community-common-5.7.11-1.el6.x86_64.rpm mysql-community-libs-5.7.11-1.el6.x86_64.rpm mysql-community-server-5.7.11-1.el6.x86_64.rpm ##安装MYSQL的社区版相关组件并解决依赖性 /etc/init.d/mysqld start ##开启数据库 cat /var/log/mysqld.log ##查看数据库运行日志 如图：
[root@server4 ~]# mysql_secure_installation ##server4,5主从节点上均要进行安全初始化
Securing the MySQL server deployment.
Enter password for user root: qt(auSF.:0lJ
The existing password for the user account root has expired. Please set a new password.
New password: GAOfang123+ ##密码设置必须超过8位，有大小写字母组合，数字，特殊符号组成，密码强度很高
Re-enter new password: GAOfang123+
... Failed! Error: Your password does not satisfy the current policy requirements
New password: GAOfang123+
Re-enter new password: GAOfang123+
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.
Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y
New password: GAOfang123+
Re-enter new password: GAOfang123+
Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL 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? (Press y|Y for Yes, any other key for No) : 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? (Press y|Y for Yes, any other key for No) : y
Success.
By default, MySQL 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? (Press y|Y for Yes, any other key for No) : 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? (Press y|Y for Yes, any other key for No) : y
Success.
All done!
在master节点：
vim /etc/my.cnf
server-id=1
log-bin=mysql-bin
/etc/init.d/mysqld restart ##重启数据库
[root@server4 ~]# mysql -p
Enter password: GAOfang123+ ##用新的root密码登陆数据库
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.11-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant replication slave on *.* to gf@'172.25.44.55' identified by 'jeffrey';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
注意：出现此类错误是因为赋权的普通用户密码强度太低
mysql> grant replication slave on *.* to gf@'172.25.44.55' identified by 'GAOfang123+'; ##符合该格式的密码系统才能默认更改成功
Query OK, 0 rows affected, 1 warning (0.09 sec)
mysql> flush privileges; ##重新加载权限表，使刚作的修改立即生效
Query OK, 0 rows affected (0.06 sec)
mysql> show master status; #查看主节点的状态，记录下Position和File的值
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> create database westos; ##创建新的数据库westos
Query OK, 1 row affected (0.08 sec)
mysql> use westos;
Database changed
mysql> create table usertb (
-> username varchar(25) not null,
-> password varchar(25) not null);
Query OK, 0 rows affected (0.94 sec)
mysql> insert into usertb value ('user1','123');
Query OK, 1 row affected (0.12 sec)
mysql> quit
Bye
vim /etc/my.cnf
server-id=1
log-bin=mysql-bin
gtid-mode=on ##启用gtid类型，否则是普通的复制架构
enforce-gtid-consistency=on ##强制GTID的一致性
/etc/init.d/mysqld restart ##重启数据库在master节点：
[root@server5 mysql]# mysql -p Enter password: GAOfang123+ Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.11 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> change master to master_host='172.25.44.44',master_user='gf',master_password='GAOfang123+',master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.24 sec) mysql> start slave; ##开启slave节点 Query OK, 0 rows affected (0.03 sec) mysql> show slave status\G; ##查看slave节点的状态，Slave_IO_Running: Yes，Slave_SQL_Running: Yes，如果都是yes ,表示从库的I/O,Slave_SQL线程都正确开启.表明数据库正在同步 如图：
mysql> show databases; ##能查看到新建的数据库westos,说明成功 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | westos | +--------------------+ 5 rows in set (0.00 sec) mysql> use westos; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from usertb; ##查看到数据表usertb里新建的user1用户及其密码说明成功 +----------+----------+ | username | password | +----------+----------+ | user1 | 123 | +----------+----------+ 1 row in set (0.00 sec) mysql> show processlist; ##查看线程运行情况 +----+-------------+-----------+--------+---------+------+--------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+--------+---------+------+--------------------------------------------------------+------------------+ | 5 | root | localhost | westos | Query | 0 | starting | show processlist | | 6 | system user | | NULL | Connect | 392 | Waiting for master to send event | NULL | | 7 | system user | | NULL | Connect | 238 | Slave has read all relay log; waiting for more updates | NULL | +----+-------------+-----------+--------+---------+------+--------------------------------------------------------+------------------+ 3 rows in set (0.00 sec) mysql> quit Bye
数据库多线程并行复制
vim /etc/my.cnf server-id=2 gtid-mode=on enforce-gtid-consistency=on slave-parallel-type=LOGICAL_CLOCK slave-parallel-workers=16 ##开启16个线程进行并行复制 master_info_repository=TABLE relay_log_info_repository=TABLE /etc/init.d/mysqld restart ##重启数据库 [root@server5 mysql]# mysql -p Enter password: GAOfang123+ Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 21 Server version: 5.7.11 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show slave status\G; ##查看slave节点的状态，Slave_IO_Running: Yes，Slave_SQL_Running: Yes，如果都是yes ,表示从库的I/O,Slave_SQL线程都正确开启.表明数据库正在同步 如图：
mysql> show processlist; ##查看线程运行情况 +----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+ | 1 | system user | | NULL | Connect | 28 | Slave has read all relay log; waiting for more updates | NULL | | 2 | system user | | NULL | Connect | 30 | Waiting for an event from Coordinator | NULL | | 3 | system user | | NULL | Connect | 30 | Waiting for master to send event | NULL | | 4 | system user | | NULL | Connect | 30 | Waiting for an event from Coordinator | NULL | | 5 | system user | | NULL | Connect | 30 | Waiting for an event from Coordinator | NULL | | 6 | system user | | NULL | Connect | 30 | Waiting for an event from Coordinator | NULL | | 7 | system user | | NULL | Connect | 30 | Waiting for an event from Coordinator | NULL | | 9 | system user | | NULL | Connect | 30 | Waiting for an event from Coordinator | NULL | | 10 | system user | | NULL | Connect | 30 | Waiting for an event from Coordinator | NULL | | 11 | system user | | NULL | Connect | 30 | Waiting for an event from Coordinator | NULL | | 12 | system user | | NULL | Connect | 30 | Waiting for an event from Coordinator | NULL | | 13 | system user | | NULL | Connect | 30 | Waiting for an event from Coordinator | NULL | | 14 | system user | | NULL | Connect | 30 | Waiting for an event from Coordinator | NULL | | 15 | system user | | NULL | Connect | 30 | Waiting for an event from Coordinator | NULL | | 16 | system user | | NULL | Connect | 30 | Waiting for an event from Coordinator | NULL | | 17 | system user | | NULL | Connect | 30 | Waiting for an event from Coordinator | NULL | | 18 | system user | | NULL | Connect | 30 | Waiting for an event from Coordinator | NULL | | 19 | system user | | NULL | Connect | 30 | Waiting for an event from Coordinator | NULL | | 21 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+ 19 rows in set (0.00 sec) ##说明成功
