如何基于生產(chǎn)環(huán)境mysql5.6.25主從部署新的mysql從庫操作指南

1,背景需求

我們注重客戶提出的每個要求，我們充分考慮每一個細(xì)節(jié)，我們積極的做好做網(wǎng)站、成都網(wǎng)站設(shè)計(jì)服務(wù)，我們努力開拓更好的視野，通過不懈的努力，成都創(chuàng)新互聯(lián)贏得了業(yè)內(nèi)的良好聲譽(yù)，這一切，也不斷的激勵著我們更好的服務(wù)客戶。主要業(yè)務(wù)：網(wǎng)站建設(shè),網(wǎng)站制作,網(wǎng)站設(shè)計(jì),微信小程序開發(fā),網(wǎng)站開發(fā),技術(shù)開發(fā)實(shí)力，DIV+CSS，PHP及ASP，ASP.Net，SQL數(shù)據(jù)庫的技術(shù)開發(fā)工程師。

由于現(xiàn)網(wǎng)的環(huán)境，經(jīng)過近期監(jiān)控，發(fā)現(xiàn)MySQL主庫的IO讀寫壓力比較大，需要部署新的MYSQL從庫，進(jìn)一步分?jǐn)侻YSQL主庫的壓力，以及現(xiàn)存MYSQL從庫的讀負(fù)載。為了減少對于MYSQL主庫的IO讀取性能沖擊，采用基于MYSQL現(xiàn)存從庫構(gòu)建新的MYSQL從庫。

2，現(xiàn)存的數(shù)據(jù)庫架構(gòu)

編號 IP地址數(shù)據(jù)庫角色

1 10.0.0.12 數(shù)據(jù)庫主庫

2 10.0.0.16 數(shù)據(jù)庫從庫1

3，準(zhǔn)備好一個新的數(shù)據(jù)庫從庫2

編號 IP地址數(shù)據(jù)庫角色

3 10.0.0.14 數(shù)據(jù)庫從庫2

4，在新的數(shù)據(jù)庫從庫2，配置server_id=3,區(qū)別于上述2個節(jié)點(diǎn)

5，在數(shù)據(jù)庫從庫1，關(guān)閉sql thread,為了實(shí)現(xiàn)MYSQLDUMP備份的數(shù)據(jù)一致性；

mysql> system hostname

standby3

mysql> stop slave;

Query OK, 0 rows affected (0.01 sec)

6，在數(shù)據(jù)庫從庫1，獲取show slave status之如下標(biāo)注的2個列

（注：用于基于這個列的值，用于后續(xù)構(gòu)建新的MYSQL從庫）

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State:

Master_Host: 10.0.0.12

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000002

Read_Master_Log_Pos: 120

Relay_Log_File: standby3-relay-bin.000004

Relay_Log_Pos: 280

Relay_Master_Log_File: binlog.000002 ---應(yīng)用主庫二進(jìn)制日志

Slave_IO_Running: No

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

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 ---應(yīng)用主庫二進(jìn)制日志的位置

Relay_Log_Space: 500

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: NULL

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: 65fddb9f-fd33-11e9-95f0-080027d1f3fc

Master_Info_File: /var/lib/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State:

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)

ERROR:

No query specified

mysql>

7，在MYSQL從庫1導(dǎo)出數(shù)據(jù)庫備份

[root@standby3 mysql]# mysqldump -uroot -psystem --databases zxydb >/dump_dir/for_create_2_slave.dump

Warning: Using a password on the command line interface can be insecure.

[root@standby3 mysql]#

8，重啟MYSQL從庫1的SQL THREAD

mysql> start slave sql_thread;

Query OK, 0 rows affected (0.01 sec)

9，把MYSQL從庫1 復(fù)制數(shù)據(jù)庫份到 MYSQL從庫2

[root@standby2mysql mysql]# mkdir -p /dump_dir

[root@standby2mysql mysql]#

[root@standby3 mysql]# scp /dump_dir/for_create_2_slave.dump root@10.0.0.14:/dump_dir/

root@10.0.0.14's password:

for_create_2_slave.dump 100% 2530 2.5KB/s 00:00

[root@standby3 mysql]#

10，在MYSQL從庫2導(dǎo)入DUMP文件

[root@standby2mysql mysql]# mysql -uroot -psystem </dump_dir/for_create_2_slave.dump

Warning: Using a password on the command line interface can be insecure.

[root@standby2mysql mysql]#

[root@standby2mysql mysql]# mysql -uroot -psystem

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 3

Server version: 5.6.25-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

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 |

| performance_schema |

| test |

| zxydb |

+--------------------+

5 rows in set (0.00 sec)

11，在MYSQL主庫創(chuàng)建用于構(gòu)建MYSQL從庫2連接MYSQL主庫的復(fù)制數(shù)據(jù)庫用戶

mysql> create user 'repl'@'10.0.0.14' identified by 'system';

Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'repl'@'10.0.0.14';

Query OK, 0 rows affected (0.00 sec)

12，在MYSQL從庫2構(gòu)建至MYSQL主庫的主從復(fù)制關(guān)系

mysql> change master to master_host='10.0.0.12',master_user='repl',master_password='system',master_log_file='binlog.000002',master_log_pos=120;

Query OK, 0 rows affected, 2 warnings (0.03 sec)

13，在MYSQL從庫2 啟動主從復(fù)制服務(wù)

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

14，在MYSQL從庫2查看主從復(fù)制運(yùn)行

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.0.0.12

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000002

Read_Master_Log_Pos: 437

Relay_Log_File: standby2mysql-relay-bin.000003

Relay_Log_Pos: 280

Relay_Master_Log_File: binlog.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:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 437

Relay_Log_Space: 938

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: 65fddb9f-fd33-11e9-95f0-080027d1f3fc

Master_Info_File: /var/lib/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)

ERROR:

No query specified

15，驗(yàn)證MYSQL主庫與新部署的MYSQL從庫2 數(shù)據(jù)同步正常

--MYSQL主庫

mysql> use zxydb;

Database changed

mysql> insert into t_go select 5,5;

Query OK, 1 row affected (0.01 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_go;

+---+------+

| a | b |

+---+------+

| 1 | 1 |

| 2 | 2 |

| 3 | 3 |

| 5 | 5 |

+---+------+

4 rows in set (0.00 sec)

---MYSQL從庫2

mysql> select * from zxydb.t_go;

+---+------+

| a | b |

+---+------+

| 1 | 1 |

| 2 | 2 |

| 3 | 3 |

| 5 | 5 |

+---+------+

4 rows in set (0.00 sec)

16，在MYSQL主庫執(zhí)行，可見現(xiàn)配置2個MYSQL從庫

mysql> show slave hosts;

+-----------+------+------+-----------+--------------------------------------+

+-----------+------+------+-----------+--------------------------------------+

| 3 | | 3306 | 1 | 699a379f-e36d-11e9-a4e2-0800274dcc79 |

| 2 | | 3306 | 1 | 891bc123-fd72-11e9-8cf5-080027dddbcd |

+-----------+------+------+-----------+--------------------------------------+

2 rows in set (0.00 sec)

大家可以關(guān)注我的微信公眾號，每天會定期發(fā)送一些數(shù)據(jù)庫相關(guān)的文章，歡迎交流。

如何基于生產(chǎn)環(huán)境mysql 5.6.25主從部署新的mysql從庫操作指南

分享標(biāo)題：如何基于生產(chǎn)環(huán)境mysql5.6.25主從部署新的mysql從庫操作指南
文章出自：http://bm7419.com/article32/pcocpc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供用戶體驗(yàn)、App開發(fā)、服務(wù)器托管、網(wǎng)站營銷、全網(wǎng)營銷推廣、云服務(wù)器

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容