MySQL双机

原创

heraleign 2018-05-29 09:06:22 ©著作权

©著作权归作者所有：来自51CTO博客作者heraleign的原创作品，请联系作者获取转载授权，否则将追究法律责任

配置两台主机MySQL双活主机： A:192.168.199.2 B:192.168.199.4

一、安装服务,两台主机执行同样的操作：

root@e2emydb02[192.168.199.4][/tmp]# tar xvf MySQL-5.6.40-1.el6.x86_64.rpm-bundle.tar root@e2emydb02[192.168.199.4][/tmp]# rpm -ivh MySQL-*.rpm --force --nodeps root@e2emydb02[192.168.199.4][/tmp]# cp /usr/share/mysql/my-default.cnf /etc/my.cnf root@e2emydb02[192.168.199.4][/tmp]# service mysql start Starting MySQL.Logging to '/var/lib/mysql/e2emydb02.err'. [ OK ] mysql 5.6 以后安装时的默认密码存放在目录：/root/.mysql_secret 1、查看密码 root@e2emydb02[192.168.199.4][/root]# more /root/.mysql_secret The random password set for the root user at Wed May 23 11:30:41 2018 (local time): 5eU4P7fz9Qj_UojJ 2、使用该密码登陆测试：
root@e2emydb02[192.168.199.4][/root]# mysql -u root -p 3、修改密码： root@e2emydb02[192.168.199.4][/root]# mysqladmin -u root -p password Enter password: New password: Confirm new password: 4、修改数据保存目录默认安装在/var/lib/mysql/ 目录修改到自己定义的目录 /etedata 执行以下操作： root@e2emydb02[192.168.199.4][/root]# service mysql stop root@e2emydb02[192.168.199.4][/root]# cp -R /var/lib/mysql /etedata 把原目录备份 root@e2emydb02[192.168.199.4][/root]# mv /var/lib/mysql /var/lib/mysql.bak 对/etedata目录赋权 root@e2emydb02[192.168.199.4][/root]# chmod 777 -R /etedata ##否则启动会报错： Starting MySQL...The server quit without updating PID file [FAILED]a/mysql/e2emydb01.pid). 错误日志：/etedata/mysql/e2emydb02.err 修改/etc/my.cnf root@e2emydb02[192.168.199.4][/root]# vi /etc/my.cnf [client] port=3306 socket=/etedata/mysql/mysql.sock [mysqld] datadir =/etedata/mysql socket=/etedata/mysql/mysql.sock sql_mode=NO_ENGINE_SUBSTITUTION

保存，重启服务 root@e2emydb02[192.168.199.4][/root]# service mysql stop Shutting down MySQL.... [ OK ] root@e2emydb02[192.168.199.4][/root]# service mysql start Starting MySQL. [ OK ] root@e2emydb02[192.168.199.4][/tmp]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.40 MySQL Community Server (GPL)

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>

5、设置服务端、客户端、结果集为UTF-8 mysql> SET character_set_client = utf8; Query OK, 0 rows affected (0.00 sec)

mysql> SET character_set_results = utf8; Query OK, 0 rows affected (0.00 sec)

mysql> SET character_set_database=utf8; Query OK, 0 rows affected (0.00 sec)

mysql> create database etedb; Query OK, 1 row affected (0.02 sec)

mysql> quit

二配置从 A-->B

至此，基础工作做好了。下面配置A-->B 1、在A主机操作： root@e2emydb01[192.168.199.2][/root]#more /etc/my.cnf [client] port=3306 socket=/etedata/mysql/mysql.sock

[mysqld] log-bin=mysql-bin binlog_format=mixed server-id = 1 read-only=0 binlog-do-db=etedb
binlog-ignore-db=information_schema binlog-ignore-db=mysql binlog-ignore-db=performance_schema binlog-ignore-db=test auto-increment-increment=2 auto-increment-offset=1

datadir =/etedata/mysql

socket=/etedata/mysql/mysql.sock 完成后需要重启服务器 root@e2emydb01[192.168.199.2][/root]#service mysql stop Shutting down MySQL.... [ OK ] root@e2emydb01[192.168.199.2][/root]#service mysql start Starting MySQL. [ OK ] root@e2emydb01[192.168.199.2][/root]#mysql -u root -p Enter password: 2、添加用户为用户授权,只有192.168.199.4可以访问

GRANT USAGE ON . TO 'repl_user'@'192.168.199.2' IDENTIFIED BY '' WITH GRANT OPTION; grant replication slave on . to 'repl_user'@'192.168.199.4' identified by ''; 执行情况如下： root@e2emydb01[192.168.199.2][/root]#mysql -u root -p Enter password: mysql> GRANT USAGE ON . TO 'repl_user'@'192.168.199.2' IDENTIFIED BY '********' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec)

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

在B主机测试： root@e2emydb02[192.168.199.4][/root]#mysql -h192.168.199.2 -urepl_user -p******** Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.6.40-log MySQL Community Server (GPL)

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>

3、获取服务器初态在A服务器执行（MYSQL命令行下）锁定表： mysql> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.00 sec) 在A主机执行导出初态： root@e2emydb01[192.168.199.2][/root]#mysqldump --master-data -uroot -p etedb >etedb.sql Enter password: 并把文件传到B服务器上： root@e2emydb01[192.168.199.2][/root]#scp etedb.sql ete@192.168.199.4:/home/ete ete@192.168.199.4's password: 然后查看初态值并解锁 mysql> show master status\G *************************** 1. row *************************** File: mysql-bin.000001 Position: 120 Binlog_Do_DB: etedb Binlog_Ignore_DB: information_schema,mysql,performance_schema,test Executed_Gtid_Set: 1 row in set (0.00 sec)

mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)

注：标红的两个值会在B服务器用到

4、在B服务器执行： root@e2emydb02[192.168.199.4][/root]#vi /etc/my.cnf [client] port=3306 socket=/etedata/mysql/mysql.sock

[mysqld] datadir =/etedata/mysql

log-bin=mysql-bin binlog_format=mixed server-id= 2

replicate-do-db=etedb replicate-ignore-db=information_schema replicate-ignore-db=mysql replicate-ignore-db=performance_schema replicate-ignore-db=test relay_log=mysqld-relay-bin log-slave-update=yes

socket=/etedata/mysql/mysql.sock sql_mode=NO_ENGINE_SUBSTITUTION

root@e2emydb02[192.168.199.4][/root]#service mysql stop Shutting down MySQL.... [ OK ] root@e2emydb02[192.168.199.4][/root]#service mysql start Starting MySQL. [ OK ] root@e2emydb02[192.168.199.4][/root]#mysql -u root -p mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.199.2', -> MASTER_USER='repl_user', -> MASTER_PASSWORD='********', -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=120; Query OK, 0 rows affected, 2 warnings (0.00 sec) 注：上面几个值是从主服务器得到的 mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.199.2 Master_User: repl_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 120 Relay_Log_File: mysqld-relay-bin.000003 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes ## 这两个值是Yes才表示OK Slave_SQL_Running: Yes ## Replicate_Do_DB: etedb Replicate_Ignore_DB: information_schema,mysql,performance_schema,test 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: 120 Relay_Log_Space: 457 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 Master_UUID: dd4d3f58-5fcb-11e8-8e32-6c92bf5e6ba0 Master_Info_File: /etedata/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec) 把刚才从A服务器同步过来的初态文件导入： root@e2emydb02[192.168.199.4][/home/ete]#cp etedb.sql /root root@e2emydb02[192.168.199.4][/home/ete]#cd root@e2emydb02[192.168.199.4][/root]#mysql -u root -p etedb<etedb.sql Enter password: root@e2emydb02[192.168.199.4][/root]#service mysql stop Shutting down MySQL.... [ OK ] root@e2emydb02[192.168.199.4][/root]#service mysql start Starting MySQL. [ OK ]

然后可以测试了在A主机执行建表和和插值： mysql> use etedb; Database changed mysql> create table a(id int); Query OK, 0 rows affected (0.01 sec)

mysql> insert into a values(1); Query OK, 1 row affected (0.00 sec) 在B主机验证： mysql> use etedb; 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> show tables; +-----------------+ | Tables_in_etedb | +-----------------+ | a | +-----------------+ 1 row in set (0.00 sec)

mysql> select * from a; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec)

三、配置B-->A

1、在B主机执行： mysql> GRANT USAGE ON . TO 'repl_user'@'192.168.199.4' IDENTIFIED BY '********' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on . to 'repl_user'@'192.168.199.2' identified by '********'; Query OK, 0 rows affected (0.00 sec) 2、修改B的配置文件： root@e2emydb02[192.168.199.4][/root]#vi /etc/my.cnf [client] port=3306 socket=/etedata/mysql/mysql.sock

[mysqld] datadir =/etedata/mysql

log-bin=mysql-bin binlog_format=mixed server-id= 2

read-only=0 binlog-do-db=etedb binlog-ignore-db=information_schema binlog-ignore-db=test binlog-ignore-db=mysql binlog-ignore-db=performance_schema auto-increment-increment=2 auto-increment-offset=2

replicate-do-db=etedb replicate-ignore-db=information_schema replicate-ignore-db=mysql replicate-ignore-db=performance_schema replicate-ignore-db=test relay_log=mysqld-relay-bin log-slave-update=yes

socket=/etedata/mysql/mysql.sock sql_mode=NO_ENGINE_SUBSTITUTION 3、查看初态参数 mysql> show master status\G *************************** 1. row *************************** File: mysql-bin.000005 Position: 120 Binlog_Do_DB: etedb Binlog_Ignore_DB: information_schema,test,mysql,performance_schema Executed_Gtid_Set: 1 row in set (0.00 sec) 4、在A服务器执行： root@e2emydb01[192.168.199.2][/root]#vi /etc/my.cnf [client] port=3306 socket=/etedata/mysql/mysql.sock

[mysqld]

log-bin=mysql-bin binlog_format=mixed server-id = 1

read-only=0 binlog-do-db=etedb

binlog-ignore-db=information_schema binlog-ignore-db=mysql binlog-ignore-db=performance_schema binlog-ignore-db=test auto-increment-increment=2 auto-increment-offset=1

replicate-do-db=etedb replicate-ignore-db=information_schema replicate-ignore-db=test replicate-ignore-db=mysql replicate-ignore-db=performance_schema relay_log=mysqld-relay-bin log-slave-update=yes

datadir =/etedata/mysql

socket=/etedata/mysql/mysql.sock 在数据库执行： root@e2emydb01[192.168.199.2][/root]#mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.40-log MySQL Community Server (GPL)

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='192.168.199.4', -> MASTER_USER='repl_user', -> MASTER_PASSWORD='********', -> MASTER_LOG_FILE='mysql-bin.000005', -> MASTER_LOG_POS=120; Query OK, 0 rows affected, 2 warnings (0.00 sec) 重启服务器： root@e2emydb01[192.168.199.2][/root]#service mysql stop Shutting down MySQL.... [ OK ] root@e2emydb01[192.168.199.2][/root]#service mysql start Starting MySQL. [ OK ] 查看数据库的状态： mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.199.4 Master_User: repl_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 1324 Relay_Log_File: mysqld-relay-bin.000003 Relay_Log_Pos: 1487 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: etedb Replicate_Ignore_DB: information_schema,test,mysql,performance_schema 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: 1324 Relay_Log_Space: 1661 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 Master_UUID: 2c61b0aa-5fe9-11e8-8ef1-6c92bf5e0138 Master_Info_File: /etedata/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec) 然后做双向测试：在B服务器执行：

mysql> create table b(id int); Query OK, 0 rows affected (0.00 sec)

mysql> insert into b values(2); Query OK, 1 row affected (0.00 sec) 在A查看： root@e2emydb01[192.168.199.2][/root]#mysql -u root -p Enter password: mysql> show tables; +-----------------+ | Tables_in_etedb | +-----------------+ | a | | b | +-----------------+ 2 rows in set (0.00 sec)

mysql> select * from b; +------+ | id | +------+ | 2 | +------+ 1 row in set (0.00 sec)