mysql的master和slave配置
這篇文章主要介紹“MySQL的master和slave配置”，在日常操作中，相信很多人在mysql的master和slave配置問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”mysql的master和slave配置”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！
網(wǎng)站建設(shè)哪家好，找創(chuàng)新互聯(lián)建站！專注于網(wǎng)頁設(shè)計、網(wǎng)站建設(shè)、微信開發(fā)、成都微信小程序、集團企業(yè)網(wǎng)站建設(shè)等服務(wù)項目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了西市免費建站歡迎大家使用！
mysql master和slave配置
一、master節(jié)點
master節(jié)點創(chuàng)建賬號并授權(quán)
mysql> create user backup identified by '1234';
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> grant all on *.* to backup;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
master節(jié)點配置兩個參數(shù)
[root@node2 ~]# vi /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
validate_password = OFF
server-id=1
log-bin=mysql-bin
重啟mysqld服務(wù)
mysql> show master status
-> ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
二、slave節(jié)點配置
[root@node3 ~]# vi /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
validate_password = OFF
log_bin = mysql-bin
server_id = 2
relay_log = mysql-relay-bin
log_slave_updates = 1
read_only = 1
~
重啟mysqld服務(wù)
[root@node3 ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@node3 ~]#
[root@node3 ~]#
[root@node3 ~]#
配置具體的參數(shù)
[root@node3 ~]# mysql -u root -proot
mysql: [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.7.10-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, 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> use mysql;
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>
mysql>
mysql>
mysql> change master to master_host='192.168.10.11',
-> master_user='backup',
-> master_password='1234',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=0;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.10.11
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
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: 4
Relay_Log_Space: 154
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: 0
Master_UUID:
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
啟動slave服務(wù)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
查看slave服務(wù)啟動是否正常
mysql>
mysql>
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.10.11
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
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: 4
Relay_Log_Space: 154
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: 1593
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID:
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 more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 160310 15:12:18
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
看到slave服務(wù)有一個沒有啟動
Slave_IO_Running: No
Slave_SQL_Running: Yes
查看mysql日志 報1593錯誤
2016-03-10T07:19:04.236362Z 6 [Warning]
2016-03-10T07:19:04.236675Z 7 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2016-03-10T07:19:04.239318Z 7 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'mysql-bin.000001' at position 4, relay log './mysql-relay-bin.000001' position: 4
2016-03-10T07:19:04.241677Z 6 [Note] Slave I/O thread for channel '': connected to master 'backup@192.168.10.11:3306',replication started in log 'mysql-bin.000001' at position 4
2016-03-10T07:19:04.249077Z 6 [ERROR] Slave I/O for channel '': , Error_code: 1593
2016-03-10T07:19:04.249110Z 6 [Note] Slave I/O thread exiting for channel '', read up to log 'mysql-bin.000001', position 4
三、1593錯誤處理
查看server_id是否重復(fù)，看到兩個節(jié)點的server_id是不一樣的。
mysql> show variables like '%server_id%';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| server_id | 1 |
| server_id_bits | 32 |
+----------------+-------+
2 rows in set (0.00 sec)
mysql> exit
Bye
mysql> show variables like '%server_id%';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| server_id | 2 |
| server_id_bits | 32 |
+----------------+-------+
2 rows in set (0.01 sec)
mysql>
mysql>
mysql>
mysql> exit
查看server-uuid是否重啟，可以看到兩個節(jié)點的uuid一樣，主要是因為我的slave虛擬機是拷貝master的
[root@node2 mysql]# more auto.cnf
[auto]
server-uuid=cdf9834e-df88-11e5-99cd-080027701d15
[root@node2 mysql]#
drwxr-x--- 2 mysql mysql 4096 Mar 4 15:08 testdb
[root@node3 mysql]# more auto.cnf
[auto]
server-uuid=cdf9834e-df88-11e5-99cd-080027701d15
修改slave節(jié)點的uuid
[root@node3 mysql]# vi auto.cnf
[auto]
server-uuid=cdf9834e-df88-11e5-99cd-080027701d16
~
~
重啟master和slave的msyql服務(wù)
[root@node2 ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@node2 ~]# mysql -u root -proot
mysql: [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.7.10-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, 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.
[root@node3 ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@node3 ~]# mysql -u root -proot
mysql: [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 5
Server version: 5.7.10-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and
重啟slave服務(wù)Slave_IO_Running、Slave_SQL_Running都正常
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.11
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000006
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000003
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: 154
Relay_Log_Space: 740
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: cdf9834e-df88-11e5-99cd-080027701d15
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 more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
mysql日志也顯示正常啟動
2016-03-10T08:16:34.330158Z 1 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.000002' at position 311 for channel ''
2016-03-10T08:16:34.330185Z 1 [Warning]
2016-03-10T08:16:34.330583Z 1 [ERROR] Slave I/O for channel '': error reconnecting to master 'backup@192.168.10.11:3306' - retry-time: 60 retries: 1, Error_code: 2003
2016-03-10T08:17:32.159968Z 2 [Note] Error reading relay log event for channel '': slave SQL thread was killed
2016-03-10T08:17:32.160582Z 1 [Note] Slave I/O thread killed during or after a reconnect done to recover from failed read
2016-03-10T08:17:32.160604Z 1 [Note] Slave I/O thread exiting for channel '', read up to log 'mysql-bin.000002', position 311
2016-03-10T08:17:48.372597Z 6 [Warning]
2016-03-10T08:17:48.373286Z 7 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2016-03-10T08:17:48.374626Z 7 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'mysql-bin.000002' at position 311, relay log './mysql-relay-bin.000004' position: 320
2016-03-10T08:17:48.378924Z 6 [Note] Slave I/O thread for channel '': connected to master 'backup@192.168.10.11:3306',replication started in log 'mysql-bin.000002' at position 311
四、驗證
在master節(jié)點創(chuàng)建一張表后，slave節(jié)點自動同步新建的表
mysql> use testdb;
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>
mysql>
mysql>
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| test |
| test1 |
+------------------+
2 rows in set (0.00 sec)
mysql> create table test2 select * from test1;
Query OK, 0 rows affected (0.05 sec)
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| test |
| test1 |
| test2 |
+------------------+
3 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> use testdb;
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>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| test |
| test1 |
+------------------+
2 rows in set (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| test |
| test1 |
| test2 |
+------------------+
3 rows in set (0.00 sec)
mysql>
mysql>
mysql>
到此，關(guān)于“mysql的master和slave配置”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>
分享文章：mysql的master和slave配置
文章路徑：http://bm7419.com/article6/jddsig.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供外貿(mào)網(wǎng)站建設(shè)、網(wǎng)站收錄、靜態(tài)網(wǎng)站、定制網(wǎng)站、標(biāo)簽優(yōu)化、網(wǎng)站設(shè)計公司
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)
- 瀘州創(chuàng)新互聯(lián)：定制網(wǎng)站建設(shè)的優(yōu)勢 2022-07-12
- 定制網(wǎng)站建設(shè)價格具體是多少？ 2021-04-30
- 模板網(wǎng)站與定制網(wǎng)站的區(qū)別 2019-09-17
- 自助建站跟定制網(wǎng)站設(shè)計哪個更好 2014-01-22
- 模板建站與高端定制網(wǎng)站有什么不同 2021-09-27
- 網(wǎng)站建設(shè)要多少錢，定制網(wǎng)站的優(yōu)勢有哪些呢？ 2021-11-29
- 快速建站與定制網(wǎng)站設(shè)計的利弊 2019-01-29
- 成都中小企業(yè)建站是定制網(wǎng)站還是選擇模板網(wǎng)站？ 2014-12-05
- 網(wǎng)站建設(shè)時間查詢網(wǎng)站建設(shè)需要多長時間定制網(wǎng)站要多久? 2016-10-31
- 上海網(wǎng)站建設(shè)定制網(wǎng)站和模板建站的對比分析 2021-10-28
- 定制網(wǎng)站相比于摸板站有何不同？ 2021-04-17
- 淺談定制網(wǎng)站制作的好處有哪些？ 2022-12-12