MariaDB10.0 instance deployment and multi-source replication Configuration

MariaDB10.0 instance deployment and multi-source replication Configuration

1. Deploy MariaDB10.0.17

1. Download and briefly describe MariaDB
Currently, MariaDB supports multi-source replication of version 10.0. The latest stable version is 10.0.17. The download link is http://mirrors.opencas.cn/mariadb/mariadb-10.0.17/source/mariadb-10.0.17.tar.gz. MariaDB and PerconaDB introduce the thread pool and disable NUMA concept in the new version of 5.5, which improves database performance a lot, while MySQL version 5.5.23 (seems to be) this concept is also available in the above versions, but it is a feature of the Enterprise Edition. This feature is not available in the open-source version. In MariaDB10, a new feature is multi-source replication, it is useful for some special scenarios, such as sharding tables for data summarization.
Note: There are a lot of attractive points in the new version, but the pitfalls have not been fully mined. If there are problems, it is difficult to find materials and solve the problems, therefore, we do not recommend that you use the latest version in the production environment. Currently, MySQL, PerconaDB, and MariaDB mainstream versions are 5.5. If you do not have special requirements, 5.5 is sufficient. Aside from the thread pool, 5.6 may not be better than 5.5 in terms of overall performance.

2. installation and deployment of MariaDB
MariaDB is basically the same as MySQL and Percona. Versions later than 5.5 are compiled and installed using 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-keys = 1_= 1-DDEFAULT_CHARSET = utf8-DDEFAULT_COLLATION =/ data/percona/tmp/mysql. sock-DENABLED_LOCAL_INFILE = ON-DENABLED_PROFILING = ON-DWITH_DEBUG = 0-DMYSQL_TCP_PORT = 3306
# Make & make install
3. configuration file of MariaDB
This configuration file applies to PerconaDB, removing the thread pool and NAMA parameters, and also applies to 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 # It automatically kills slow SQL statements that exceed 1 s. PerconaDB5.6 is supported and is not recommended. If you use it to communicate with the business side, it is recommended to use it dynamically in special circumstances, the default value is 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_cached = 16384
Sort_buffer_size = 2 M
Join_buffer_size = 2 M
Read_buffer_size = 4 M
Read_rnd_buffer_size = 4 M
Key_buffer_size = 64 M
Myisam_sort_buffer_size = 64 M
Tmp_table_size = 256 M
Max_heap_table_size = 256 M
Open_files_limit = 65535
 
##### Network ######################
Max_allowed_packet = 16 M
Interactive_timeout = 28400
Wait_timeout = 28400
Max-connections = 1000
Max_user_connections = 0
Max_connect_errorrs = 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 = 128 M
Innodb_log_files_in_group = 3
Innodb_log_file_size = 512 M
Innodb_log_buffer_size = 8 M
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
######### Thread pool, which demonstrates outstanding database performance under high concurrency and high load ##
Thread_handling = pool-of-threads
 
###### NUMA #########################
Innodb_buffer_pool_populate = 1
 
##################################
[Mysqldump]
Quick
Max_allowed_packet = 16 M
 
[Mysql]
No-auto-rehash
Default-character-set = utf8
 
[Myisamchk]
Key_buffer_size = 256 M
Sort_buffer_size = 256 M
Read_buffer = 2 M
Write_buffer = 2 M
 
[Mysqld_safe]
###### Closed numa ###########
Flush_caches
Numa_interleave
 
[Mysqlhotcopy]
Interactive_timeout = 28400
4. Database initialization and startup
The database initialization and startup scripts are as follows:
#/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 # Add to system startup Item
Ii. configuration of MariaDB multi-source Replication
1. initialize database users
Users who initialize multi-source slave databases are recommended to delete all initial users and create four users: DBA root account, backup users, monitoring users, master-slave synchronization users.
Related permissions and commands for creating a user are as follows:
# Create a user
Grant allprivileges on *. * TO 'root' @ 'localhost' identified by '123456' with grant option;
Grantreplication slave, replication client on *. * TO 'replicater '@ '192. 168.2.100' identified by '2016 ';
GRANTSELECT, RELOAD, show databases, SUPER, lock tables, replication client, SHOWVIEW, event on *. * TO 'backup '@ 'localhost' identified by '123 ';
GRANTSELECT, PROCESS, SUPER, replication slave, replication client on *. * TO 'monitor' @ '192. 0.0.1 'identified by '2016 ';
# We recommend that you use dropuser xxxx @ xxxxx to delete a user.
As a DBA, it is best to back up any online write operations and leave yourself a back-up path.
2. Back up databases of multiple master Databases
For multi-source replication of MariaDB, the names of databases in the master database must be different. For backup, you only need to back up the database to be synchronized, databases that do not need to be synchronized can be filtered out using parameters from multiple sources. mysql, information_schema, and cece_schema are not synchronized by default.
The backup command is as follows:
/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 &
# Back up data in the background
3. Import backup data
Import the backup data of each master database to the multi-source slave database. The command is as follows:
/Data/mariadb/bin/mysql-uxxxx-pxxxxx db_name <db_name_20150320. SQL &
4. Establish a master-slave relationship
The emphasis here is on connection_name, that is, the connection_name added in the previous syntax. If connection_name is not added, the default value is null. Connection_name is the identifier used to conveniently manage a single master-slave relationship. The command for establishing a master-slave relationship is as follows:
Mysql> changemaster 'percona 'to master_host =' 192. 168.2.100 ', MASTER_PORT = 3307, master_user = 'repl', master_password = 'xxxxxx', master_log_file = 'mysql-bin.000019', master_log_pos = 120;
Percona is connection_name. Synchronize one connection_name for each source and execute the preceding SQL commands separately.
The command to start master-slave synchronization is:
Mysql> start slave 'percona ';
You can also start it together after creating all the synchronization relationships:
Mysql> start all slaves;
Command to stop a single synchronization:
Mysql> stop slave 'percona ';
The command to stop all synchronization tasks is:
Mysql> stop all slaves;
When the synchronization is established and runs normally, the relay-log will be generated, the relay-log name is: relay-log-percona.000001, will automatically add connection_name.
You can use show all slaves status to view all the synchronization statuses. The status information is as follows:
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:

For more details, please continue to read the highlights on the next page:

-------------------------------------- Split line --------------------------------------

Install LAMP (Apache with MariaDB and PHP) in CentOS/RHEL/Scientific Linux 6)

Implementation of MariaDB Proxy read/write splitting

How to compile and install the MariaDB database in Linux

Install MariaDB database using yum in CentOS

Install MariaDB and MySQL

How to migrate MySQL 5.5 database to MariaDB 10 on Ubuntu

Ubuntu 14.04 (Trusty) Server install MariaDB http://www.bkjia.com/Linux/2014-12/110048htm

1 2 Next Page