歡迎來到Linux教程網
Linux教程網
Linux教程網
Linux教程網
Linux教程網 >> Linux基礎 >> 關於Linux >> Innobackup mysql 多實例環境搭建主從同步

Innobackup mysql 多實例環境搭建主從同步

日期:2017/3/1 12:08:22   编辑:關於Linux

mysql 的主從搭建大家有很多種方式,傳統的 mysqldump 方式是很多人的選擇之一。但對於較大的數據庫則該方式並非理想的選擇。使用 Xtrabackup 可以快速輕松的構建 mysql 主從架構。本文描述了使用innobackupex快速來搭建主從架構。

需求:

(1)用戶通過 innobackupex 從 mysql-master備份數據
(2)將主庫備份的數據在 mysql-slave 還原數據
(3)設置 mysql-master 和 mysql-slave 數據實時同步

這裡寫圖片描述

環境:<喎?http://www.2cto.com/kf/ware/vc/" target="_blank" class="keylink">vc3Ryb25nPjxiciAvPg0KbXlzcWwtbWFzdGVyo7oxMC4xLjAuNTA8YnIgLz4NCm15c3FsLXNsYXZlo7oxMC4xLjAuNjA8YnIgLz4NCm15c3FsILDmsb6jujUuNi4xNDwvcD4NCjxwPjxzdHJvbmc+17yxuKO6PC9zdHJvbmc+PGJyIC8+DQpwZXJjb25hLXh0cmFiYWNrdXAgsLLXsLD8o6zM4bmpIGlubm9iYWNrdXBleCC5pL7fPC9wPg0KPHA+PHN0cm9uZz7PwsPmv6rKvKO6PC9zdHJvbmc+PC9wPg0KPHA+PHN0cm9uZz7Su6GisLLXsCBwZXJjb25hLXh0cmFiYWNrdXCjqNb3tNO2vNDo0qqjrNb308PAtLG4t92jrLTT08PAtLu51K2jqTwvc3Ryb25nPjwvcD4NCjxwPs/C1Ni12Na3o7podHRwOi8vd3d3LnBlcmNvbmEuY29tL2Rvd25sb2Fkcy9YdHJhQmFja3VwLzwvcD4NCjxwPltyb290QG1hc3RlciB+XSMgeXVtIC15IGluc3RhbGwgcGVybCBwZXJsLWRldmVsIGxpYmFpbyBsaWJhaW8tZGV2ZWwgcGVybC1UaW1lLUhpUmVzIHBlcmwtREJELU15U1FMIGxpYmV2LWRldmVsPGJyIC8+DQpbcm9vdEBtYXN0ZXIgfl0jIHJwbSAtaXZoIHBlcmNvbmEteHRyYWJhY2t1cC0yLjMuNC0xLmVsNi54ODZfNjQucnBtPC9wPg0KPGhyIC8+DQo8cD48c3Ryb25nPrb+oaLW97/isbi33cr9vt2jqG15c3FsLW1hc3RlcqO6MTAuMS4wLjUwo6k8L3N0cm9uZz48L3A+DQo8cD5bcm9vdEBtYXN0ZXIgfl0jIG1rZGlyIC90bXAvYmFja3VwPGJyIC8+DQpbcm9vdEBtYXN0ZXIgfl0jIHRpbWUgaW5ub2JhY2t1cGV4ICZuZGFzaDtkZWZhdWx0cy1maWxlPS9ldGMvbXkuY25mICZuZGFzaDtkZWZhdWx0cy1ncm91cD1teXNxbGQxICZuZGFzaDt1c2VyPXJvb3QgJm5kYXNoO3Bhc3N3b3JkPSQoY2F0IC9kYXRhL3NhdmUvbXlzcWxfcm9vdCkgL3RtcC9iYWNrdXAvPC9wPg0KPGhyIC8+DQo8cD48c3Ryb25nPrb+oaK08rD8o6y0q7Xdtb2007/io6hteXNxbC1zbGF2ZaO6MTAuMS4wLjYwo6k8L3N0cm9uZz48L3A+DQo8cD5bcm9vdEBtYXN0ZXIgdG1wXSMgdGFyIHpjZiBiYWNrdXAudGd6IGJhY2t1cC88YnIgLz4NCltyb290QG1hc3RlciB0bXBdIyBzY3AgYmFja3VwLnRneiAxMC4xLjAuNjA6L3RtcDwvcD4NCjxociAvPg0KPHA+PHN0cm9uZz7I/aGiu7nUrbTT1ve/4rG4t921xMr9vt08L3N0cm9uZz48L3A+DQo8cD48c3Ryb25nPr3isPyjujwvc3Ryb25nPjxiciAvPg0KW3Jvb3RAc2xhdmUgdG1wXSMgdGFyIHp4ZiBiYWNrdXAudGd6PC9wPg0KPHA+sbi33b7JtcTK/b7dxL/CvKOssqLH0tDCvajSu7j2yv2+3cS/wryjrL340NC7udSttNPW97/isbi33bXEyv2+3aO6PGJyIC8+DQpbcm9vdEBzbGF2ZSB0bXBdIyBtdiAvdXNyL2xvY2FsL215c3FsL2RhdGEvZGJkYXRhXzMzMDYvIC91c3IvbG9jYWwvbXlzcWwvZGF0YS9kYmRhdGFfMzMwNl9iYWs8YnIgLz4NCltyb290QHNsYXZlIHRtcF0jIG1rZGlyIC1wIC91c3IvbG9jYWwvbXlzcWwvZGF0YS9kYmRhdGFfMzMwNjwvcD4NCjxwPjxzdHJvbmc+u7nUrcr9vt2jujwvc3Ryb25nPjxiciAvPg0KW3Jvb3RAc2xhdmUgZGF0YV0jIHRpbWUgaW5ub2JhY2t1cGV4ICZuZGFzaDtkZWZhdWx0cy1maWxlPS9ldGMvbXkuY25mICZuZGFzaDtkZWZhdWx0cy1ncm91cD1teXNxbGQxICZuZGFzaDt1c2VyPXJvb3QgJm5kYXNoO3Bhc3N3b3JkPSQoY2F0IC9kYXRhL3NhdmUvbXlzcWxfcm9vdCkgJm5kYXNoO2FwcGx5LWxvZyAvdG1wL2JhY2t1cC8yMDE2LTA0LTE5XzE2LTU3LTM3LzwvcD4NCjxwPltyb290QHNsYXZlIGRhdGFdIyB0aW1lIGlubm9iYWNrdXBleCAmbmRhc2g7ZGVmYXVsdHMtZmlsZT0vZXRjL215LmNuZiAmbmRhc2g7ZGVmYXVsdHMtZ3JvdXA9bXlzcWxkMSAmbmRhc2g7dXNlcj1yb290ICZuZGFzaDtwYXNzd29yZD0kKGNhdCAvZGF0YS9zYXZlL215c3FsX3Jvb3QpICZuZGFzaDtjb3B5LWJhY2sgL3RtcC9iYWNrdXAvMjAxNi0wNC0xOV8xNi01Ny0zNy88L3A+DQo8cD48c3Ryb25nPtei0uKjujwvc3Ryb25nPjxiciAvPg0KJm5kYXNoO2FwcGx5LWxvZyDRoc/utcTD/MHuysfXvLG41NrSu7j2sbi33cnPxvS2r215c3Fst/7O8TxiciAvPg0KJm5kYXNoO2NvcHktYmFjayDRoc/utcTD/MHutNOxuLfdxL/CvL+9sbTK/b7dLMv30v0syNXWvrW9bXkuY25mzsS8/sDvuea2qLXEs/XKvM671sM8L3A+DQo8cD48c3Ryb25nPsrayKijrLKix9LG9LavIG15c3FsILf+zvGjujwvc3Ryb25nPjxiciAvPg0KW3Jvb3RAc2xhdmUgZGF0YV0jIGNob3duIG15c3FsOm15c3FsIC1SIC91c3IvbG9jYWwvbXlzcWwvZGF0YS9kYmRhdGFfMzMwNi88YnIgLz4NCltyb290QHNsYXZlIGRhdGFdIyAvdXNyL2xvY2FsL215c3FsL2Jpbi9teXNxbGRfbXVsdGkgc3RhcnQgMTwvcD4NCjxociAvPg0KPHA+PHN0cm9uZz7LxKGitNO/4sno1sOyosb0tq/W97TTzayyvTwvc3Ryb25nPjwvcD4NCjxwPs7Sw8fPyMC0v7S/tCBpbm5vYmFja3VwZXggsbi33brzyfqzybXE0ru33c7EvP6jrMDvw+a8x9TYwcvW97TTy/nQ6NKqtcQgTUFTVEVSX0xPR19GSUxFILrNIE1BU1RFUl9MT0dfUE9TPC9wPg0KPHA+PGltZyBhbHQ9"這裡寫圖片描述" src="http://www.2cto.com/uploadfile/Collfiles/20160420/2016042009103968.png" title="\" />

登錄數據庫

這裡寫圖片描述

輸入 mysql-master 的 user,host,port,password,log_file,log_pos 等信息,接著啟動主從命令:start slave;

這裡寫圖片描述

查看主從情況(發現是正常的):

root@(none) 17:15>
root@(none) 17:15>
root@(none) 17:15>show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.1.0.50
                  Master_User: backup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 151
               Relay_Log_File: mysql-bin.000002
                Relay_Log_Pos: 314
        Relay_Master_Log_File: mysql-bin.000001
             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: 151
              Relay_Log_Space: 512
              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: 7018e49e-060c-11e6-ba4c-000c2943a57f
             Master_Info_File: mysql.slave_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

root@(none) 17:15>

五、mysql-master 和 mysql-slave 配置文件

mysql-master 配置檔:

#!/bin/bash

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = root
password = yunjee0515ueopro1234

[mysqld1]
basedir = /usr/local/mysql
character-set-server = utf8
port = 3306
socket = /tmp/mysql_3306.sock
datadir = /usr/local/mysql/data/dbdata_3306
pid-file = /usr/local/mysql/data/dbdata_3306/mysql.pid
log-error = /usr/local/mysql/data/dbdata_3306/mysql.err
server-id = 1
skip-character-set-client-handshake                                     #忽略應用程序想要設置的其他字符集
init-connect='SET NAMES utf8'                                           #連接時執行的SQL
character-set-server=utf8                                               #服務端默認字符集
wait_timeout=1800                                                       #請求的最大連接時間
interactive_timeout=1800                                                #和上一參數同時修改才會生效
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES                     #sql模式
log-bin = mysql-bin                                                     #打開二進制功能,MASTER主服務器必須打開此項
relay-log = mysql-bin                                                   #文件名格式
relay-log-index = mysql-bin.index                                       #index文件名
##MySQL 5.6.10版本提供了更方便的基於GTID的復制功能,MySQL可以通過GTID自動識別上次同步的點,極大地方便了運維人員,減少出錯的幾率。
binlog-format=ROW                                                       #binlog格式
log-slave-updates=true                                                  #表示如果一個MASTER掛掉的話,另外一個馬上接管
gtid-mode=on                                                            #用於啟動GTID及滿足附屬的其它需求
enforce-gtid-consistency=true                                           #
#report-port=port                           #從屬服務器的端口
#report-host=host                           #從屬服務器的主機名
master-info-repository=TABLE                                            #啟用此兩項,可用於實現在崩潰時保證二進制及從服務器安全的功能
relay-log-info-repository=TABLE                                         #
sync-master-info=1                                                      #啟用之可確保無信息丟失
slave-parallel-workers=2                                                #設定從服務器的SQL線程數;0表示關閉多線程復制功能
binlog-checksum=CRC32                                                   #
master-verify-checksum=1                                                #
slave-sql-verify-checksum=1                                             #啟用復制有關的所有校驗功能
binlog-rows-query-log_events=1                                          ###MySQL 5.6.10版本提供了更方便的基於GTID的復制功能,MySQL可以通過GTID自動識別上次同步的點,極大地方便了運維人員,減少出錯的幾率。
expire_logs_day=5                           #超過5天的binlog刪除  
max_binlog_size=104857600
#replicate-ignore-db = mysql                        #忽略不同步主從的數據庫
#replicate-ignore-db = information_schema
#replicate-ignore-db = performance_schema
#replicate-ignore-db = test
#replicate-ignore-db = zabbix
#replicate-ignore-db = sysbench
#replicate-ignore-db = db_3306
##slave-skip-errors=0                           #主從忽略錯誤數
##注釋掉,使用默認設置
#skip-external-locking
##innodb_force_recovery = 1
key_buffer_size = 256M
max_allowed_packet = 100M
#table_open_cache = 1024
#sort_buffer_size = 128M
#net_buffer_length = 8K
#read_buffer_size = 128M
#read_rnd_buffer_size = 256M
#myisam_sort_buffer_size = 32M
#character-set-server = utf8
skip-name-resolve
max_connections = 10000
##慢查詢設置
slow-query-log=on
long_query_time = 2
#log-queries-not-using-indexes
##注釋掉,使用默認設置
## For InnoDB
innodb_buffer_pool_size = 1G
#innodb_additional_mem_pool_size = 128M
## Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 128M
#innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
#innodb_file_per_table=1

[mysqld2]
basedir = /usr/local/mysql
character-set-server = utf8
port = 3307
socket = /tmp/mysql_3307.sock
datadir = /usr/local/mysql/data/dbdata_3307
pid-file = /usr/local/mysql/data/dbdata_3307/mysql.pid
log-error = /usr/local/mysql/data/dbdata_3307/mysql.err
server-id = 1
skip-character-set-client-handshake                                     #忽略應用程序想要設置的其他字符集
init-connect='SET NAMES utf8'                                           #連接時執行的SQL
character-set-server=utf8                                               #服務端默認字符集
wait_timeout=1800                                                       #請求的最大連接時間
interactive_timeout=1800                                                #和上一參數同時修改才會生效
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES                     #sql模式
log-bin = mysql-bin                                                     #打開二進制功能,MASTER主服務器必須打開此項
relay-log = mysql-bin                                                   #文件名格式
relay-log-index = mysql-bin.index                                       #index文件名
#MySQL 5.6.10版本提供了更方便的基於GTID的復制功能,MySQL可以通過GTID自動識別上次同步的點,極大地方便了運維人員,減少出錯的幾率。
binlog-format=ROW                                                       #binlog格式
log-slave-updates=true                                                  #表示如果一個MASTER掛掉的話,另外一個馬上接管
gtid-mode=on                                                            #用於啟動GTID及滿足附屬的其它需求
enforce-gtid-consistency=true                                           #
#report-port=port                                                       #從屬服務器的端口
#report-host=host                                                       #從屬服務器的主機名
master-info-repository=TABLE                                            #啟用此兩項,可用於實現在崩潰時保證二進制及從服務器安全的功能
relay-log-info-repository=TABLE                                         #
sync-master-info=1                                                      #啟用之可確保無信息丟失
slave-parallel-workers=2                                                #設定從服務器的SQL線程數;0表示關閉多線程復制功能
binlog-checksum=CRC32                                                   #
master-verify-checksum=1                                                #
slave-sql-verify-checksum=1                                             #啟用復制有關的所有校驗功能
binlog-rows-query-log_events=1                                          ###MySQL 5.6.10版本提供了更方便的基於GTID的復制功能,MySQL可以通過GTID自動識別上次同步的點,極大地方便了運維人員,減少出錯的幾率。
expire_logs_day=5                                                       #超過5天的binlog刪除
max_binlog_size=104857600
#replicate-ignore-db = mysql                                             #忽略不同步主從的數據庫
#replicate-ignore-db = information_schema
#replicate-ignore-db = performance_schema
#replicate-ignore-db = test
#replicate-ignore-db = zabbix
#replicate-ignore-db = sysbench
#replicate-ignore-db = db_3307
##slave-skip-errors=0                                                   #主從忽略錯誤數
##注釋掉,使用默認設置
#skip-external-locking
##innodb_force_recovery = 1
key_buffer_size = 256M
max_allowed_packet = 100M
#table_open_cache = 1024
#sort_buffer_size = 128M
#net_buffer_length = 8K
#read_buffer_size = 128M
#read_rnd_buffer_size = 256M
#myisam_sort_buffer_size = 32M
#character-set-server = utf8
skip-name-resolve
max_connections = 10000
##慢查詢設置
slow-query-log=on
long_query_time = 2
#log-queries-not-using-indexes
##注釋掉,使用默認設置
## For InnoDB
innodb_buffer_pool_size = 1G
#innodb_additional_mem_pool_size = 128M
## Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 128M
#innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
#innodb_file_per_table=1

[mysqld3]
basedir = /usr/local/mysql
character-set-server = utf8
port = 3308
socket = /tmp/mysql_3308.sock
datadir = /usr/local/mysql/data/dbdata_3308
pid-file = /usr/local/mysql/data/dbdata_3308/mysql.pid
log-error = /usr/local/mysql/data/dbdata_3308/mysql.err
server-id = 1
skip-character-set-client-handshake                                     #忽略應用程序想要設置的其他字符集
init-connect='SET NAMES utf8'                                           #連接時執行的SQL
character-set-server=utf8                                               #服務端默認字符集
wait_timeout=1800                                                       #請求的最大連接時間
interactive_timeout=1800                                                #和上一參數同時修改才會生效
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES                     #sql模式
log-bin = mysql-bin                                                     #打開二進制功能,MASTER主服務器必須打開此項
relay-log = mysql-bin                                                   #文件名格式
relay-log-index = mysql-bin.index                                       #index文件名
#MySQL 5.6.10版本提供了更方便的基於GTID的復制功能,MySQL可以通過GTID自動識別上次同步的點,極大地方便了運維人員,減少出錯的幾率。
binlog-format=ROW                                                       #binlog格式
log-slave-updates=true                                                  #表示如果一個MASTER掛掉的話,另外一個馬上接管
gtid-mode=on                                                            #用於啟動GTID及滿足附屬的其它需求
enforce-gtid-consistency=true                                           #
#report-port=port                                                       #從屬服務器的端口
#report-host=host                                                       #從屬服務器的主機名
master-info-repository=TABLE                                            #啟用此兩項,可用於實現在崩潰時保證二進制及從服務器安全的功能
relay-log-info-repository=TABLE                                         #
sync-master-info=1                                                      #啟用之可確保無信息丟失
slave-parallel-workers=2                                                #設定從服務器的SQL線程數;0表示關閉多線程復制功能
binlog-checksum=CRC32                                                   #
master-verify-checksum=1                                                #
slave-sql-verify-checksum=1                                             #啟用復制有關的所有校驗功能
binlog-rows-query-log_events=1                                          ###MySQL 5.6.10版本提供了更方便的基於GTID的復制功能,MySQL可以通過GTID自動識別上次同步的點,極大地方便了運維人員,減少出錯的幾率。
expire_logs_day=5                                                       #超過5天的binlog刪除
max_binlog_size=104857600
#replicate-ignore-db = mysql                                             #忽略不同步主從的數據庫
#replicate-ignore-db = information_schema
#replicate-ignore-db = performance_schema
#replicate-ignore-db = test
#replicate-ignore-db = zabbix
#replicate-ignore-db = sysbench
#replicate-ignore-db = db_3308
##slave-skip-errors=0                                                   #主從忽略錯誤數
##注釋掉,使用默認設置
#skip-external-locking
##innodb_force_recovery = 1
key_buffer_size = 256M
max_allowed_packet = 100M
#table_open_cache = 1024
#sort_buffer_size = 128M
#net_buffer_length = 8K
#read_buffer_size = 128M
#read_rnd_buffer_size = 256M
#myisam_sort_buffer_size = 32M
#character-set-server = utf8
skip-name-resolve
max_connections = 10000
##慢查詢設置
slow-query-log=on
long_query_time = 2
#log-queries-not-using-indexes
##注釋掉,使用默認設置
## For InnoDB
innodb_buffer_pool_size = 1G
#innodb_additional_mem_pool_size = 128M
## Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 128M
#innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
#innodb_file_per_table=1

[mysqldump]
quick
max_allowed_packet = 256M

[mysql]
no-auto-rehash
prompt=\\u@\\d \\R:\\m>
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

mysql-slave 配置檔:

#!/bin/bash

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = root
password = yunjee0515ueopro1234

[mysqld1]
basedir = /usr/local/mysql
character-set-server = utf8
port = 3306
socket = /tmp/mysql_3306.sock
datadir = /usr/local/mysql/data/dbdata_3306
pid-file = /usr/local/mysql/data/dbdata_3306/mysql.pid
log-error = /usr/local/mysql/data/dbdata_3306/mysql.err
server-id = 2
skip-character-set-client-handshake                                     #忽略應用程序想要設置的其他字符集
init-connect='SET NAMES utf8'                                           #連接時執行的SQL
character-set-server=utf8                                               #服務端默認字符集
wait_timeout=1800                                                       #請求的最大連接時間
interactive_timeout=1800                                                #和上一參數同時修改才會生效
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES                     #sql模式
log-bin = mysql-bin                                                     #打開二進制功能,MASTER主服務器必須打開此項
relay-log = mysql-bin                                                   #文件名格式
relay-log-index = mysql-bin.index                                       #index文件名
##MySQL 5.6.10版本提供了更方便的基於GTID的復制功能,MySQL可以通過GTID自動識別上次同步的點,極大地方便了運維人員,減少出錯的幾率。
binlog-format=ROW                                                       #binlog格式
log-slave-updates=true                                                  #表示如果一個MASTER掛掉的話,另外一個馬上接管
gtid-mode=on                                                            #用於啟動GTID及滿足附屬的其它需求
enforce-gtid-consistency=true                                           #
report-port=3306                            #從屬服務器的端口
report-host=10.1.0.60                           #從屬服務器的主機名
master-info-repository=TABLE                                            #啟用此兩項,可用於實現在崩潰時保證二進制及從服務器安全的功能
relay-log-info-repository=TABLE                                         #
sync-master-info=1                                                      #啟用之可確保無信息丟失
slave-parallel-workers=2                                                #設定從服務器的SQL線程數;0表示關閉多線程復制功能
binlog-checksum=CRC32                                                   #
master-verify-checksum=1                                                #
slave-sql-verify-checksum=1                                             #啟用復制有關的所有校驗功能
binlog-rows-query-log_events=1                                          ###MySQL 5.6.10版本提供了更方便的基於GTID的復制功能,MySQL可以通過GTID自動識別上次同步的點,極大地方便了運維人員,減少出錯的幾率。
expire_logs_day=5                           #超過5天的binlog刪除  
max_binlog_size=104857600
#replicate-ignore-db = mysql                        #忽略不同步主從的數據庫
#replicate-ignore-db = information_schema
#replicate-ignore-db = performance_schema
#replicate-ignore-db = test
#replicate-ignore-db = zabbix
#replicate-ignore-db = sysbench
#replicate-ignore-db = db_3306
##slave-skip-errors=0                           #主從忽略錯誤數
##注釋掉,使用默認設置
#skip-external-locking
##innodb_force_recovery = 1
key_buffer_size = 256M
max_allowed_packet = 100M
#table_open_cache = 1024
#sort_buffer_size = 128M
#net_buffer_length = 8K
#read_buffer_size = 128M
#read_rnd_buffer_size = 256M
#myisam_sort_buffer_size = 32M
#character-set-server = utf8
skip-name-resolve
max_connections = 10000
##慢查詢設置
slow-query-log=on
long_query_time = 2
#log-queries-not-using-indexes
##注釋掉,使用默認設置
## For InnoDB
innodb_buffer_pool_size = 1G
#innodb_additional_mem_pool_size = 128M
## Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 128M
#innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
#innodb_file_per_table=1

[mysqld2]
basedir = /usr/local/mysql
character-set-server = utf8
port = 3307
socket = /tmp/mysql_3307.sock
datadir = /usr/local/mysql/data/dbdata_3307
pid-file = /usr/local/mysql/data/dbdata_3307/mysql.pid
log-error = /usr/local/mysql/data/dbdata_3307/mysql.err
server-id = 2
skip-character-set-client-handshake                                     #忽略應用程序想要設置的其他字符集
init-connect='SET NAMES utf8'                                           #連接時執行的SQL
character-set-server=utf8                                               #服務端默認字符集
wait_timeout=1800                                                       #請求的最大連接時間
interactive_timeout=1800                                                #和上一參數同時修改才會生效
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES                     #sql模式
log-bin = mysql-bin                                                     #打開二進制功能,MASTER主服務器必須打開此項
relay-log = mysql-bin                                                   #文件名格式
relay-log-index = mysql-bin.index                                       #index文件名
#MySQL 5.6.10版本提供了更方便的基於GTID的復制功能,MySQL可以通過GTID自動識別上次同步的點,極大地方便了運維人員,減少出錯的幾率。
binlog-format=ROW                                                       #binlog格式
log-slave-updates=true                                                  #表示如果一個MASTER掛掉的話,另外一個馬上接管
gtid-mode=on                                                            #用於啟動GTID及滿足附屬的其它需求
enforce-gtid-consistency=true                                           #
report-port=3307                                                       #從屬服務器的端口
report-host=10.1.0.60                                                       #從屬服務器的主機名
master-info-repository=TABLE                                            #啟用此兩項,可用於實現在崩潰時保證二進制及從服務器安全的功能
relay-log-info-repository=TABLE                                         #
sync-master-info=1                                                      #啟用之可確保無信息丟失
slave-parallel-workers=2                                                #設定從服務器的SQL線程數;0表示關閉多線程復制功能
binlog-checksum=CRC32                                                   #
master-verify-checksum=1                                                #
slave-sql-verify-checksum=1                                             #啟用復制有關的所有校驗功能
binlog-rows-query-log_events=1                                          ###MySQL 5.6.10版本提供了更方便的基於GTID的復制功能,MySQL可以通過GTID自動識別上次同步的點,極大地方便了運維人員,減少出錯的幾率。
expire_logs_day=5                                                       #超過5天的binlog刪除
max_binlog_size=104857600
#replicate-ignore-db = mysql                                             #忽略不同步主從的數據庫
#replicate-ignore-db = information_schema
#replicate-ignore-db = performance_schema
#replicate-ignore-db = test
#replicate-ignore-db = zabbix
#replicate-ignore-db = sysbench
#replicate-ignore-db = db_3307
##slave-skip-errors=0                                                   #主從忽略錯誤數
##注釋掉,使用默認設置
#skip-external-locking
##innodb_force_recovery = 1
key_buffer_size = 256M
max_allowed_packet = 100M
#table_open_cache = 1024
#sort_buffer_size = 128M
#net_buffer_length = 8K
#read_buffer_size = 128M
#read_rnd_buffer_size = 256M
#myisam_sort_buffer_size = 32M
#character-set-server = utf8
skip-name-resolve
max_connections = 10000
##慢查詢設置
slow-query-log=on
long_query_time = 2
#log-queries-not-using-indexes
##注釋掉,使用默認設置
## For InnoDB
innodb_buffer_pool_size = 1G
#innodb_additional_mem_pool_size = 128M
## Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 128M
#innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
#innodb_file_per_table=1

[mysqld3]
basedir = /usr/local/mysql
character-set-server = utf8
port = 3308
socket = /tmp/mysql_3308.sock
datadir = /usr/local/mysql/data/dbdata_3308
pid-file = /usr/local/mysql/data/dbdata_3308/mysql.pid
log-error = /usr/local/mysql/data/dbdata_3308/mysql.err
server-id = 2
skip-character-set-client-handshake                                     #忽略應用程序想要設置的其他字符集
init-connect='SET NAMES utf8'                                           #連接時執行的SQL
character-set-server=utf8                                               #服務端默認字符集
wait_timeout=1800                                                       #請求的最大連接時間
interactive_timeout=1800                                                #和上一參數同時修改才會生效
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES                     #sql模式
log-bin = mysql-bin                                                     #打開二進制功能,MASTER主服務器必須打開此項
relay-log = mysql-bin                                                   #文件名格式
relay-log-index = mysql-bin.index                                       #index文件名
#MySQL 5.6.10版本提供了更方便的基於GTID的復制功能,MySQL可以通過GTID自動識別上次同步的點,極大地方便了運維人員,減少出錯的幾率。
binlog-format=ROW                                                       #binlog格式
log-slave-updates=true                                                  #表示如果一個MASTER掛掉的話,另外一個馬上接管
gtid-mode=on                                                            #用於啟動GTID及滿足附屬的其它需求
enforce-gtid-consistency=true                                           #
report-port=3308                                                       #從屬服務器的端口
report-host=10.1.0.60                                                       #從屬服務器的主機名
master-info-repository=TABLE                                            #啟用此兩項,可用於實現在崩潰時保證二進制及從服務器安全的功能
relay-log-info-repository=TABLE                                         #
sync-master-info=1                                                      #啟用之可確保無信息丟失
slave-parallel-workers=2                                                #設定從服務器的SQL線程數;0表示關閉多線程復制功能
binlog-checksum=CRC32                                                   #
master-verify-checksum=1                                                #
slave-sql-verify-checksum=1                                             #啟用復制有關的所有校驗功能
binlog-rows-query-log_events=1                                          ###MySQL 5.6.10版本提供了更方便的基於GTID的復制功能,MySQL可以通過GTID自動識別上次同步的點,極大地方便了運維人員,減少出錯的幾率。
expire_logs_day=5                                                       #超過5天的binlog刪除
max_binlog_size=104857600
#replicate-ignore-db = mysql                                             #忽略不同步主從的數據庫
#replicate-ignore-db = information_schema
#replicate-ignore-db = performance_schema
#replicate-ignore-db = test
#replicate-ignore-db = zabbix
#replicate-ignore-db = sysbench
#replicate-ignore-db = db_3308
##slave-skip-errors=0                                                   #主從忽略錯誤數
##注釋掉,使用默認設置
#skip-external-locking
##innodb_force_recovery = 1
key_buffer_size = 256M
max_allowed_packet = 100M
#table_open_cache = 1024
#sort_buffer_size = 128M
#net_buffer_length = 8K
#read_buffer_size = 128M
#read_rnd_buffer_size = 256M
#myisam_sort_buffer_size = 32M
#character-set-server = utf8
skip-name-resolve
max_connections = 10000
##慢查詢設置
slow-query-log=on
long_query_time = 2
#log-queries-not-using-indexes
##注釋掉,使用默認設置
## For InnoDB
innodb_buffer_pool_size = 1G
#innodb_additional_mem_pool_size = 128M
## Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 128M
#innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
#innodb_file_per_table=1

[mysqldump]
quick
max_allowed_packet = 256M

[mysql]
no-auto-rehash
prompt=\\u@\\d \\R:\\m>
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout
Copyright © Linux教程網 All Rights Reserved