[Blog recommendations] MariaDB10.0 instance deployment and multi-source replication configuration (1)

[Blog recommendations] MariaDB10.0 instance deployment and multi-source replication configuration (1)

This blog post is from the Bkjia blog Master Wang Weibo. If you have any questions, please go to the blog homepage for an interactive discussion! Blog: http://wangtianfa.blog.51cto.com/5281093/1621797

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 -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. 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