【博文推薦】MariaDB10.0實(shí)例部署和多源復(fù)制配置
| 本博文出自51CTO博客王偉博主，有任何問(wèn)題請(qǐng)進(jìn)入博主頁(yè)面互動(dòng)討論! |
一、部署MariaDB10.0.17
1、MariaDB下載和簡(jiǎn)單說(shuō)明
當(dāng)前MariaDB支持多源復(fù)制的版本為10.0的版本，最新穩(wěn)定版本為10.0.17，下載連接為：http://mirrors.opencas.cn/mariadb/mariadb-10.0.17/source/mariadb-10.0.17.tar.gz。MariaDB和PerconaDB在5.5的新版中引進(jìn)來(lái)線程池和關(guān)閉NUMA的概念，對(duì)數(shù)據(jù)庫(kù)性能提高不少，而MySQL的版本在5.5.23(貌似是)以上的版本中也有這個(gè)概念，但是是屬于企業(yè)版的功能，開(kāi)源版本中沒(méi)有這個(gè)功能;MariaDB10的版本中多了一個(gè)新功能就是多源復(fù)制，對(duì)于一些特殊的場(chǎng)景比較實(shí)用：如sharding過(guò)的表做數(shù)據(jù)匯總等，一般對(duì)匯總統(tǒng)計(jì)比較有用。
注：新的版本固然有很多吸引人的地方，但是其中的坑還沒(méi)有挖完，有問(wèn)題的話不易查找材料，不易于解決，所以不建議生產(chǎn)環(huán)境使用最新的版本。目前MySQL、PerconaDB和MariaDB主流版本是5.5，如果非特殊需要，5.5的足夠用，拋開(kāi)線程池來(lái)說(shuō)，5.6的版本在整體的性能上未必比5.5的好。
2、MariaDB的安裝部署
MariaDB和MySQL、Percona的基本上完全一樣，5.5以后的版本采用cmake的方式編譯安裝：
- #tar –zxfmariadb-10.0.17.tar.gz
- #cdmariadb-10.0.17
- #cmake .-DCMAKE_INSTALL_PREFIX=/data/percona/ -DMYSQL_DATADIR=/data/percona/data-DSYSCONFDIR=/data/percona/etc -DWITH_INNOBASE_STORAGE_ENGINE=1-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 -DDEFAULT_CHARSET=utf8-DDEFAULT_COLLATION=utf8_general_ci-DMYSQL_UNIX_ADDR=/data/percona/tmp/mysql.sock -DENABLED_LOCAL_INFILE=ON-DENABLED_PROFILING=ON -DWITH_DEBUG=0 -DMYSQL_TCP_PORT=3306
- #make&& make install
3、MariaDB的配置文件
這個(gè)配置文件適用于PerconaDB，去掉線程池和NAMA的參數(shù)，同樣適用于MySQL：
- [client]
- port = 3306
- socket = /data/mariadb/tmp/mysql.sock
- default-character-set= utf8
- [mysqld]
- port = 3306
- bind-address= 0.0.0.0
- lower_case_table_names = 1
- basedir = /data/mariadb
- datadir = /data/mariadb/data
- tmpdir = /data/mariadb/tmp
- socket = /data/mariadb/tmp/mysql.sock
- #######################################
- log-output = FILE
- log-error = /data/mariadb/log/error.log
- #general_log
- general_log_file = /data/mariadb/log/mysql.log
- pid-file = /data/mariadb/data/mysql.pid
- slow-query-log
- slow_query_log_file = /data/mariadb/log/slow.log
- tmpdir = /data/mariadb/tmp/
- long_query_time = 0.1
- #max_statement_time = 1000 #自動(dòng)殺死超過(guò)1s的慢sql，PerconaDB5.6支持，不建議使用，如使用的和業(yè)務(wù)方溝通好，建議在特殊的情況動(dòng)態(tài)配置使用，默認(rèn)是0，不限制。
- sync_binlog = 1
- skip-external-locking
- skip-name-resolve
- default-storage-engine= INNODB
- character-set-server= utf8
- wait_timeout= 28400
- back_log = 1024
- #########################
- thread_concurrency = 16
- thread_cache_size = 512
- table_open_cache = 16384
- table_definition_cache = 16384
- sort_buffer_size = 2M
- join_buffer_size = 2M
- read_buffer_size = 4M
- read_rnd_buffer_size = 4M
- key_buffer_size = 64M
- myisam_sort_buffer_size= 64M
- tmp_table_size = 256M
- max_heap_table_size = 256M
- open_files_limit = 65535
- #####Network ######################
- max_allowed_packet = 16M
- interactive_timeout = 28400
- wait_timeout = 28400
- max-connections = 1000
- max_user_connections = 0
- max_connect_errors = 100
- ######Repl #####################
- server-id = 1
- report-host = 172.16.183.56
- log-bin = mysql-bin
- binlog_format = mixed
- expire_logs_days = 7
- relay-log = relay-log
- #replicate-wild-do-table= zabbix.%
- #replicate-wild-do-table= zabbix_server.%
- replicate_wild_ignore_table=mysql.%
- replicate_wild_ignore_table=test.%
- log_slave_updates
- skip-slave-start
- #slave-net-timeout = 10
- #rpl_semi_sync_master_enabled = 1
- #rpl_semi_sync_master_wait_no_slave = 1
- #rpl_semi_sync_master_timeout = 1000
- #rpl_semi_sync_slave_enabled = 1
- relay_log_recovery = 1
- ##### Innodb ###########
- innodb_data_home_dir = /data/mariadb/data
- innodb_data_file_path = ibdata1:2G;ibdata2:2G:autoextend
- innodb_autoextend_increment = 500
- innodb_log_group_home_dir = /data/mariadb/data
- innodb_buffer_pool_size = 8G
- innodb_buffer_pool_dump_at_shutdown= 1
- innodb_buffer_pool_load_at_startup= 1
- innodb_buffer_pool_instances = 8
- innodb_additional_mem_pool_size= 128M
- innodb_log_files_in_group = 3
- innodb_log_file_size = 512M
- innodb_log_buffer_size = 8M
- innodb_flush_log_at_trx_commit = 1
- innodb_lock_wait_timeout = 120
- innodb_flush_method = O_DIRECT
- innodb_max_dirty_pages_pct = 75
- innodb_io_capacity = 1000
- innodb_thread_concurrency = 0
- innodb_thread_sleep_delay = 500
- innodb_concurrency_tickets = 1000
- innodb_open_files = 65535
- innodb_file_per_table = 1
- #########線程池,在高并發(fā)高負(fù)載情況下表現(xiàn)出出色的數(shù)據(jù)庫(kù)性能 ##
- thread_handling = pool-of-threads
- ######NUMA #########################
- innodb_buffer_pool_populate = 1
- ##################################
- [mysqldump]
- quick
- max_allowed_packet= 16M
- [mysql]
- no-auto-rehash
- default-character-set=utf8
- [myisamchk]
- key_buffer_size= 256M
- sort_buffer_size= 256M
- read_buffer= 2M
- write_buffer= 2M
- [mysqld_safe]
- ######CLOSED NUMA ###########
- flush_caches
- numa_interleave
- [mysqlhotcopy]
- interactive_timeout = 28400
4、數(shù)據(jù)庫(kù)初始化和啟動(dòng)
數(shù)據(jù)庫(kù)初始化和啟動(dòng)腳本如下：
- #/data/mariadb/scripts/mysql_install_db--basedir=/data/mariadb --datadir=/data/mariadb/data --defaults-file=/data/mariadb/etc/my.cnf--user=mysql
- #/data/mariadb/bin/mysqld_safe--defaults-file=/data/mariadb/etc/my.cnf --user=mysql &
- #echo “/data/mariadb/bin/mysqld_safe--defaults-file=/data/mariadb/etc/my.cnf --user=mysql &”>>/etc/rc.local #加入到系統(tǒng)啟動(dòng)項(xiàng)中
#p#
二、MariaDB多源復(fù)制相關(guān)配置
1、初始化數(shù)據(jù)庫(kù)用戶
初始化多源從庫(kù)的用戶，建議刪除初始所有用戶，建立4個(gè)用戶：DBA root賬戶，備份用戶，監(jiān)控用戶，主從同步用戶。
創(chuàng)建用戶的相關(guān)權(quán)限和命令如下：
- #創(chuàng)建用戶
- GRANT ALLPRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY IDENTIFIED BY '123456'WITH GRANT OPTION;
- GRANTREPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicater'@'192.168.2.100'IDENTIFIED BY '123456';
- GRANTSELECT, RELOAD, SHOW DATABASES, SUPER, LOCK TABLES, REPLICATION CLIENT, SHOWVIEW, EVENT ON *.* TO 'backup'@'localhost' IDENTIFIED BY '123456';
- GRANTSELECT, PROCESS, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO'monitor'@'127.0.0.1' IDENTIFIED BY '123456';
- #刪除用戶建議使用dropuser xxxx@xxxxx;這樣刪除比較徹底。
作為DBA，線上的任何寫操作最好做好備份，給自己留個(gè)后路
2、備份多個(gè)主庫(kù)的數(shù)據(jù)庫(kù)
MariaDB的多源復(fù)制，要求各個(gè)同步主庫(kù)中的數(shù)據(jù)庫(kù)名字各不相同，備份的話只備份需要同步的數(shù)據(jù)庫(kù)即可，不需要同步的數(shù)據(jù)庫(kù)可以在多源的從庫(kù)中使用參數(shù)過(guò)濾掉，默認(rèn)不會(huì)同步information_schema、performance_schema。
備份命令如下：
- /data/mariadb/bin/mysqldump--default-character-set=utf8 --hex-blob -R --log-error=/var/log/backup-log--single-transaction --master-data=2 -uxxxx -pxxxx –B db_name > db_name_20150320.sql &
- #放入后臺(tái)備份
3、導(dǎo)入備份數(shù)據(jù)
分別導(dǎo)入各個(gè)主庫(kù)的備份數(shù)據(jù)導(dǎo)多源的從庫(kù)中，命令如下：
- /data/mariadb/bin/mysql–uxxxx –pxxxxx db_name < db_name_20150320.sql &
4、建立主從關(guān)系
這里重點(diǎn)在connection_name,也就是在以前的語(yǔ)法上增加了connection_name,如果沒(méi)加connection_name,那么默認(rèn)的就是空。connection_name為標(biāo)識(shí)，主要是方便用于管理單個(gè)主從關(guān)系。建立主從關(guān)系的命令如下：
- Mysql>changemaster 'percona' to master_host='192.168.2.100',MASTER_PORT=3307,master_user='repl', master_password='xxxxxxx',master_log_file='mysql-bin.000019', master_log_pos=120;
- 其中的percona為connection_name。每個(gè)源的同步一個(gè)connection_name，分別執(zhí)行上述sql命令。
啟動(dòng)主從同步的命令為：
- Mysql>START SLAVE 'percona';
也可以在建立全部的同步關(guān)系后一起啟動(dòng)：
- Mysql>START ALL SLAVES;
停止單個(gè)同步的命令：
- Mysql>STOP SLAVE 'percona';
停止全部的同步的命令為：
- Mysql>STOP ALL SLAVES;
當(dāng)同步建立并正常運(yùn)行時(shí)，會(huì)產(chǎn)生relay-log，relay-log的名字為：relay-log-percona.000001,會(huì)自動(dòng)的加上connection_name。
可以使用show all slaves status來(lái)查看所有的同步狀態(tài)，狀態(tài)信息如下：
- MariaDB[(none)]> show all slaves status\G
- ***************************1. row ***************************
- Connection_name: percona
- Slave_SQL_State: Slave has readall relay log; waiting for the slave I/O thread to update it
- Slave_IO_State: Waiting formaster to send event
- Master_Host: 192.168.2.200
- Master_User: repl
- Master_Port: 3307
- Connect_Retry: 60
- Master_Log_File: mysql-bin.000021
- Read_Master_Log_Pos: 450752689
- Relay_Log_File:relay-log-percona.000011
- Relay_Log_Pos: 135537605
- Relay_Master_Log_File: mysql-bin.000021
- 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: mysql.%,test.%
- Last_Errno: 0
- Last_Error:
- Skip_Counter: 0
- Exec_Master_Log_Pos: 450752689
- Relay_Log_Space: 135537904
- 。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
- Master_Server_Id: 111156
- Master_SSL_Crl:
- Master_SSL_Crlpath:
- Using_Gtid: No
- Gtid_IO_Pos:
- Retried_transactions: 0
- Max_relay_log_size: 1073741824
- Executed_log_entries: 106216
- Slave_received_heartbeats: 12
- Slave_heartbeat_period: 1800.000
- Gtid_Slave_Pos:
- ***************************2. row ***************************
- Connection_name: percona
- Slave_SQL_State: Slave has readall relay log; waiting for the slave I/O thread to update it
- Slave_IO_State: Waiting formaster to send event
- Master_Host: 192.168.2.201
- Master_User: repl
- Master_Port: 3307
- Connect_Retry: 60
- Master_Log_File: mysql-bin.000021
- Read_Master_Log_Pos: 450752689
- Relay_Log_File:relay-log-percona.000011
- Relay_Log_Pos: 135537605
- Relay_Master_Log_File: mysql-bin.000021
- 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: mysql.%,test.%
- Last_Errno: 0
- Last_Error:
- Skip_Counter: 0
- Exec_Master_Log_Pos: 450752689
- Relay_Log_Space: 135537904
- 。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
- Master_Server_Id: 111156
- Master_SSL_Crl:
- Master_SSL_Crlpath:
- Using_Gtid: No
- Gtid_IO_Pos:
- Retried_transactions: 0
- Max_relay_log_size: 1073741824
- Executed_log_entries: 106216
- Slave_received_heartbeats: 12
- Slave_heartbeat_period: 1800.000
- Gtid_Slave_Pos:
#p#
三、MariaDB多源復(fù)制的管理
1、多源復(fù)制的管理命令
多源的復(fù)制的管理和一般的主從復(fù)制管理基本一樣，常用的命令如下，具體用途一看就明白，不在贅述：
- CHANGEMASTER 'connection_name';
- FLUSHRELAY LOGS 'connection_name';
- RESETSLAVE 'connection_name';
- RESETSLAVE 'connection_name' ALL;
- SHOWRELAYLOG 'connection_name' EVENTS;
- SHOWSLAVE 'connection_name' STATUS;
- SHOW ALLSLAVES STATUS;
- STARTSLAVE 'connection_name';
- START ALLSLAVES;
- STOPSLAVE 'connection_name';
- STOP ALLSLAVES;
2、多源復(fù)制新添加的狀態(tài)和變量
- Show global status中的新變量： Com_start_all_slaves執(zhí)行 START ALL SLAVES 命令的次數(shù)。
- Com_start_slave 執(zhí)行 START SLAVE 命令的次數(shù)。取代了Com_slave_start.
- Com_stop_slave 執(zhí)行 STOP SLAVE 命令的次數(shù)。取代了Com_slave_stop.
- Com_stop_all_slaves 執(zhí)行 STOP ALL SLAVES 命令的次數(shù)
- SHOW ALL SLAVES STATUS 有以下的新的列:
- Connection_name master的連接名。 這是第一個(gè)變量
- Slave_SQL_State SQL 線程的狀態(tài)
- Retried_transactions這個(gè)連接重試事務(wù)的次數(shù)。
- Max_relay_log_size 這個(gè)連接的最大的relay日志的大小。
- Executed_log_entriesslave已經(jīng)指向了多少個(gè)日志條目。
- Slave_received_heartbeats從master獲得了多少個(gè)心跳。
- Slave_heartbeat_period多久從master請(qǐng)求一個(gè)心跳包(以秒計(jì)算)。
3、多源復(fù)制中增加的新文件
被多源復(fù)制使用的新文件的基本準(zhǔn)則是：他們有在擴(kuò)展名前被冠以連接名前綴的和原來(lái)的中繼日志文件類似的名字。主要的例外是，保存所有連接名字的文件master-info-file 被簡(jiǎn)單的命名為帶有 multi- 前綴的 master-info-file 。當(dāng)你使用多源復(fù)制的時(shí)候，下面的文件將被創(chuàng)建：
- multi-master-info-file master-info-file (一般是master.info) 帶上了 multi- 前綴。這里面是所有使用中的master連接信息。
- master-info-file-connection_name.extension包含了當(dāng)前master應(yīng)用到slave的位置。擴(kuò)展名一般是 .info
- relay-log-connection_name.xxxxxrelay-log 有了一個(gè) connection_name 的前綴. xxxxx 是 relay log 的編號(hào)。這里面保存的是從master讀取的復(fù)制數(shù)據(jù)。
- relay-log-index-connection_name.extension包含可用的 relay-log-connection_name.xxxxx 文件的名字。擴(kuò)展名一般是 .index
- relay-log-info-file-connection_name.extension包含relay日志中的當(dāng)前master的位置。擴(kuò)展名一般是 .info
當(dāng)創(chuàng)建這些文件的時(shí)候，連接名被轉(zhuǎn)化成小寫的，并且其中所有的特殊字符都被轉(zhuǎn)化了，就和mysql表名中的特殊字符被轉(zhuǎn)化一樣。這樣做是為了方便文件名可以在不同系統(tǒng)上移植。
提示：
你只需要指定log-base-name ,所有的其他變量將會(huì)使用這個(gè)作為前綴，而不用為mysqld指定relay-log, relay-log-index,general-log, slow-log, log-bin, log-bin-index這些的名字。
其他事項(xiàng)
所有slave的錯(cuò)誤信息都會(huì)加上connection name,然后被寫入到error log，ER_MASTER_INFO和WARN_NO_MASTER_INFO現(xiàn)在會(huì)加上connection_name，這里沒(méi)有沖突的解決方案,我們假設(shè)所有的master之間沒(méi)有沖突。所有執(zhí)行的命令都被存儲(chǔ)在正常的binary log里面。如果你server variable log_warnings>1,那么你就會(huì)收到一些multi-master-info文件更新的信息。showslave status;看見(jiàn)的第一行是增加的,叫做connection_name。reset slave命令現(xiàn)在會(huì)刪除所有的relay-log文件。
4、多源復(fù)制的典型案例和使用限制
典型的使用案例:
- 將多個(gè)master的數(shù)據(jù)整合到一個(gè)slave上,方面查詢分析。
- 將多個(gè)mariadb/mysql服務(wù)器的數(shù)據(jù)整合到一個(gè)slave,方便備份。
受限的事項(xiàng):
- 一個(gè)slave最多可以有64個(gè)master;
- 每個(gè)活躍的連接會(huì)創(chuàng)建兩個(gè)線程(和mariadb復(fù)制相同);
- 你需要確認(rèn)所有的master需要有不同的server-id;
- max_relay_log_size在啟動(dòng)后修改是不能生效的;
- innodb-recovery-update-relay-log值對(duì)默認(rèn)的復(fù)制連接生效,這個(gè)參數(shù)是xtradb的特新用來(lái)存儲(chǔ)relaylog的位置號(hào)。但是這個(gè)方案存在安全隱患,我們不推薦使用;
- Slave_net_timeout對(duì)所有參數(shù)有效,我們不檢查它是否小于Slave_heartbeat_period,因?yàn)閷?duì)多主復(fù)制來(lái)說(shuō)沒(méi)有特別意義;
- multi-source現(xiàn)在還不支持semisync。
5、MariaDB多源復(fù)制跳過(guò)復(fù)制錯(cuò)誤的處理
MariaDB多源復(fù)制其中的一個(gè)復(fù)制同步失敗不會(huì)影響其他的復(fù)制，那么怎么處理其中的一個(gè)復(fù)制失敗那?簡(jiǎn)單的處理就是跳過(guò)失敗的報(bào)錯(cuò)。跳過(guò)報(bào)錯(cuò)信息和一般的復(fù)制還是有點(diǎn)區(qū)別的。如其中的一個(gè)復(fù)制名字為r1，想要r1同步正常，則需要忽略即跳過(guò)該錯(cuò)誤。
- MariaDB[r1]> stop slave 'r1';
- Query OK,0 rows affected (0.12 sec)
- MariaDB[r1]> set @@default_master_connection='r1'; #這里是重點(diǎn)：指定一個(gè)通道，然后用單通道的sql_slave_skip_counter。
- Query OK,0 rows affected (0.00 sec)
- MariaDB[r1]> select @@default_master_connection;
- +-----------------------------+
- |@@default_master_connection |
- +-----------------------------+
- | r1 |
- +-----------------------------+
- 1 row inset (0.00 sec)
- MariaDB[r1]> SET GLOBAL sql_slave_skip_counter =1;
- Query OK,0 rows affected (0.00 sec)
- MariaDB[r1]> start slave 'r1';
- Query OK,0 rows affected (0.00 sec)
