建站
咨詢
售后
建站咨詢
新聞中心
如何進(jìn)行MySQL雙機(jī)操作，很多新手對(duì)此不是很清楚，為了幫助大家解決這個(gè)難題，下面小編將為大家詳細(xì)講解，有這方面需求的人可以來學(xué)習(xí)下，希望你能有所收獲。
為遼寧等地區(qū)用戶提供了全套網(wǎng)頁設(shè)計(jì)制作服務(wù)，及遼寧網(wǎng)站建設(shè)行業(yè)解決方案。主營(yíng)業(yè)務(wù)為網(wǎng)站制作、做網(wǎng)站、遼寧網(wǎng)站設(shè)計(jì)，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠(chéng)的服務(wù)。我們深信只要達(dá)到每一位用戶的要求，就會(huì)得到認(rèn)可，從而選擇與我們長(zhǎng)期合作。這樣，我們也可以走得更遠(yuǎn)！
配置兩臺(tái)主機(jī)MySQL雙活
主機(jī)：
A:192.168.199.2
B:192.168.199.4
一、安裝服務(wù),兩臺(tái)主機(jī)執(zhí)行同樣的操作：
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 以后 安裝時(shí)的默認(rèn)密碼存放在目錄：/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、使用該密碼登陸測(cè)試：
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、修改數(shù)據(jù)保存目錄
默認(rèn)安裝在/var/lib/mysql/ 目錄
修改到自己定義的目錄 /etedata
執(zhí)行以下操作：
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
對(duì)/etedata目錄賦權(quán)
root@e2emydb02[192.168.199.4][/root]# chmod 777 -R /etedata ##否則啟動(dòng)會(huì)報(bào)錯(cuò)： Starting MySQL...The server quit without updating PID file [FAILED]a/mysql/e2emydb01.pid).
錯(cuò)誤日志：/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
保存，重啟服務(wù)
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、設(shè)置服務(wù)端、客戶端、結(jié)果集為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
至此，基礎(chǔ)工作做好了。
下面配置A-->B
1、在A主機(jī)操作：
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
完成后需要重啟服務(wù)器
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、添加用戶
為用戶授權(quán),只有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 '****';
執(zhí)行情況如下：
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主機(jī)測(cè)試：
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、獲取服務(wù)器初態(tài)
在A服務(wù)器執(zhí)行（MYSQL命令行下）鎖定表：
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
在A主機(jī)執(zhí)行導(dǎo)出初態(tài)：
root@e2emydb01[192.168.199.2][/root]#mysqldump --master-data -uroot -p etedb >etedb.sql
Enter password:
并把文件傳到B服務(wù)器上：
root@e2emydb01[192.168.199.2][/root]#scp etedb.sql ete@192.168.199.4:/home/ete
ete@192.168.199.4's password:
然后查看初態(tài)值并解鎖
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)
注：標(biāo)紅的兩個(gè)值會(huì)在B服務(wù)器用到
4、在B服務(wù)器執(zhí)行：
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)
注：上面幾個(gè)值是從主服務(wù)器得到的
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 ## 這兩個(gè)值是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服務(wù)器同步過來的初態(tài)文件導(dǎo)入：
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 ]
然后可以測(cè)試了
在A主機(jī)執(zhí)行建表和和插值：
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主機(jī)驗(yàn)證：
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主機(jī)執(zhí)行：
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、查看初態(tài)參數(shù)
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服務(wù)器執(zhí)行：
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
在數(shù)據(jù)庫執(zhí)行：
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)
重啟服務(wù)器：
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 ]
查看數(shù)據(jù)庫的狀態(tài)：
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)
然后做雙向測(cè)試：
在B服務(wù)器執(zhí)行：
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)
四：最后，在兩臺(tái)服務(wù)器上創(chuàng)建可遠(yuǎn)程訪問的用戶
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)
看完上述內(nèi)容是否對(duì)您有幫助呢？如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章，請(qǐng)關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝您對(duì)創(chuàng)新互聯(lián)的支持。
文章名稱：如何進(jìn)行MySQL雙機(jī)操作
分享鏈接：http://www.ef60e0e.cn/article/podscc.html
