当前位置:Gxlcms > mysql > MariaDB10.0实例部署和多源复制配置

MariaDB10.0实例部署和多源复制配置

时间:2021-07-01 10:21:17 帮助过:13人阅读

MariaDB10.0 实例部署和多源复制配置 一、部署 MariaDB10.0.17 1 、 MariaDB 下载和简单说明 当前 MariaDB 支持多源复制的版本为 10.0 的版本,最新稳定版本为 10.0.17 ,下载连接为: http://mirrors.opencas.cn/mariadb/mariadb-10.0.17/source/mariadb-10

MariaDB10.0实例部署和多源复制配置

一、部署MariaDB10.0.17

1MariaDB下载和简单说明

当前MariaDB支持多源复制的版本为10.0的版本,最新稳定版本为10.0.17,下载连接为:http://mirrors.opencas.cn/mariadb/mariadb-10.0.17/source/mariadb-10.0.17.tar.gzMariaDBPerconaDB5.5的新版中引进来线程池和关闭NUMA的概念,对数据库性能提高不少,而MySQL的版本在5.5.23(貌似是)以上的版本中也有这个概念,但是是属于企业版的功能,开源版本中没有这个功能;MariaDB10的版本中多了一个新功能就是多源复制,对于一些特殊的场景比较实用:如sharding过的表做数据汇总等,一般对汇总统计比较有用。

注:新的版本固然有很多吸引人的地方,但是其中的坑还没有挖完,有问题的话不易查找材料,不易于解决,所以不建议生产环境使用最新的版本。目前MySQLPerconaDBMariaDB主流版本是5.5,如果非特殊需要,5.5的足够用,抛开线程池来说,5.6的版本在整体的性能上未必比5.5的好。

2MariaDB的安装部署

MariaDBMySQLPercona的基本上完全一样,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

3MariaDB的配置文件

这个配置文件适用于PerconaDB,去掉线程池和NAMA的参数,同样适用于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 #自动杀死超过1s的慢sqlPerconaDB5.6支持,不建议使用,如使用的和业务方沟通好,建议在特殊的情况动态配置使用,默认是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

#########线程池,在高并发高负载情况下表现出出色的数据库性能 ##

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

prompt = "MySQL \u@[\d]>"


[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、数据库初始化和启动

数据库初始化和启动脚本如下:

#/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 #加入到系统启动项中

二、MariaDB多源复制相关配置

1、初始化数据库用户

初始化多源从库的用户,建议删除初始所有用户,建立4个用户:DBA root账户,备份用户,监控用户,主从同步用户。

创建用户的相关权限和命令如下:

#创建用户

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,线上的任何写操作最好做好备份,给自己留个后路

2、备份多个主库的数据库

MariaDB的多源复制,要求各个同步主库中的数据库名字各不相同,备份的话只备份需要同步的数据库即可,不需要同步的数据库可以在多源的从库中使用参数过滤掉,默认不会同步information_schemaperformance_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 &

#放入后台备份

3、导入备份数据

分别导入各个主库的备份数据导多源的从库中,命令如下:

/data/mariadb/bin/mysql–uxxxx –pxxxxx db_name < db_name_20150320.sql &

4、建立主从关系

这里重点在connection_name,也就是在以前的语法上增加了connection_name,如果没加connection_name,那么默认的就是空。connection_name为标识,主要是方便用于管理单个主从关系。建立主从关系的命令如下:

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;

其中的perconaconnection_name。每个源的同步一个connection_name,分别执行上述sql命令。

启动主从同步的命令为:

Mysql>START SLAVE 'percona';

也可以在建立全部的同步关系后一起启动:

Mysql>START ALL SLAVES;

停止单个同步的命令:

Mysql>STOP SLAVE 'percona';

停止全部的同步的命令为:

Mysql>STOP ALL SLAVES;

当同步建立并正常运行时,会产生relay-logrelay-log的名字为:relay-log-percona.000001,会自动的加上connection_name

可以使用show all slaves status来查看所有的同步状态,状态信息如下:

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_Crlp

人气教程排行