如何进行MySQL双机操作

如何进行MySQL双机操作，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。

为辽宁等地区用户提供了全套网页设计制作服务，及辽宁网站建设行业解决方案。主营业务为网站制作、做网站、辽宁网站设计，以传统方式定制建设网站，并提供域名空间备案等一条龙服务，秉承以专业、用心的态度为用户提供真诚的服务。我们深信只要达到每一位用户的要求，就会得到认可，从而选择与我们长期合作。这样，我们也可以走得更远！

配置两台主机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)

Copyright (c) 2000, 2018, 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>

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> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| etedb              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 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 -h292.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)

Copyright (c) 2000, 2018, 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>

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 /rootroot@e2emydb02[192.168.199.4][/home/ete]#cd
br/>root@e2emydb02[192.168.199.4][/home/ete]#cd
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)

Copyright (c) 2000, 2018, 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='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)

四：最后，在两台服务器上创建可远程访问的用户

mysql> GRANT USAGE ON . TO 'ete'@'%' IDENTIFIED BY '**' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON . TO 'ete'@'%' IDENTIFIED BY '**' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

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

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注创新互联行业资讯频道，感谢您对创新互联的支持。