MariaDB主从(中继)从配置,mariadb主从
环境说明(见最下方注解可不锁表):Oracle Linux 7.5
MariaDB 10.2.14
Master DB:192.168.168.10
Slave-1 DB(relay) DB:192.168.168.11 172.16.216.11
Slave-2 DB:172.16.216.12
1.安装依赖包
yum -y install make cmake gcc gcc-c++ autoconf automake zlib* libxml* ncurses-devel libtool-ltdl-devel* bison libevent openssl-devel openssl
2.安装配置主mariadb
1)创建mariadb安装目录和数据存储目录
mkdir -p /opt/mariadb10/data
2)创建用户和用户组与赋予数据存放目录权限
groupadd -g 86 mysql
useradd -u 86 mysql -g mysql -d /opt/mariadb10 -s /sbin/nologin -M
chown -R mysql:root /opt/mariadb10
3)安装mariadb
删除系统自带mariadb信息
rpm -qa|grep mariadb-libs
rpm -e mariadb-libs-5.5.56-2.el7.x86_64 --nodeps
find -H /etc/ | grep my.c
rm -rf /etc/my.cnf /etc/my.cnf.d/
只剩以下两个文件
/etc/pki/tls/certs/make-dummy-cert
/etc/pki/tls/certs/renew-dummy-cert
mariadb下载地址
tar -zxvf mariadb-10.2.14.tar.gz
cd mariadb-10.2.14
cmake . -DCMAKE_INSTALL_PREFIX=/opt/mariadb10 \
-DMYSQL_DATADIR=/opt/mariadb10/data \
-DSYSCONFDIR=/etc \
-DMYSQL_UNIX_ADDR=/opt/mariadb10/data/mysql.sock \
-DENABLED_LOCAL_INFILE=1 \ #启用加载本地数据
-DWITH_INNOBASE_STORAGE_ENGINE=1 \ #支持InnoDB引擎
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \ #支持ARCHIVE引擎
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ #支持BLACKHOLE引擎
-DWITHOUT_TOKUDB=1 \ #不安装tokudb引擎
-DMYSQL_TCP_PORT=3389 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DEXTRA_CHARSETS=all \ #支持所有扩展字符支持
-DDEFAULT_CHARSET=utf8 \ #默认字符集为utf8
-DDEFAULT_COLLATION=utf8_general_ci \ #默认字符校对utf8
-DMYSQL_USER=mysql \ #指定mariadb启动用户
-DWITH_SAFEMALLOC=OFF \
-DWITH_DEBUG=0
make && make install
4)设置环境变量及初始化数据库
vi /etc/profile.d/mariadb.sh #设置mariadb环境变量
MARIADB_HOME=/opt/mariadb10
export PATH=$MARIADB_HOME/bin:$PATH
source /etc/profile.d/mariadb.sh
初始化数据库
/opt/mariadb10/scripts/mysql_install_db --user=mysql --basedir=/opt/mariadb10 --datadir=/opt/mariadb10/data
5)复制配置文件
cp support-files/my-large.cnf /etc/my.cnf
cp support-files/mysql.server /etc/init.d/mysqld
chmod u+x /etc/init.d/mysqld
vi /etc/init.d/mysqld
将
basedir=
datadir=
改为
basedir=/opt/mariadb10
datadir=/opt/mariadb10/data
chkconfig --add mysqld #加入开机启动项
chkconfig mysqld on
6)启动数据库
/etc/init.d/mysqld start
7)初始化mariadb
/opt/mariadb10/bin/mysql_secure_installation
Enter current password for root (enter for none): #回车
Set root password? [Y/n] y #是否设置root密码
New password: mariadb
Re-enter new password: mariadb
Remove anonymous users? [Y/n] #删除匿名账号
Disallow root login remotely? [Y/n] #是否禁止root账号远程登录,生产环境中一定要禁止
Remove test database and access to it? [Y/n] y #是否清除测试数据库
Reload privilege tables now? [Y/n] y #重载
8)测试是否能进入数据库
mysql -uroot -p‘mariadb’ -P3389
9)创建日志存储目录
mkdir /var/log/mariadb/
chown mysql.root /var/log/mariadb/
查询日志:query log
慢查询日志:slow query log #查询时长超出指定界限
错误日志:error log
二进制日志:binary log #此中存储要发生改变或潜在发生改变的语句
中继日志:reley log
事务日志:transaction log
10)my.cnf配置
#password = your_password
port = 3389
socket = /opt/mariadb10/data/mysql.sock
# Here follows entries for some specific programs
# The MariaDB server
[mysqld]
port = 3389
socket = /opt/mariadb10/data/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
innodb_file_per_table = on
skip_name_resolve = on
expire_logs_days = 15
max_connections = 3000
sync-binlog = 1
log_slave_updates = on
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 2 #服务器CPU核数*2
# binary logging is required for replication
log-bin = mysql-bin
log-bin-index = mysql-bin
# binary logging format - mixed recommended
binlog_format = mixed
# but will not function as a master if omitted
server-id = 1
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_buffer_pool_size = 4G #设置为占内存的70%-80%,如服务器16G内存设置值为4G
innodb_flush_method = O_DIRECT
#output different types of log information
general_log = on
general_log_file = /var/log/mariadb/general.log
log_error = /var/log/mariadb/error.log
slow_query_log = on
slow_query_log_file = /var/log/mariadb/slow_query.log
long_query_time = 1
#GTID master to slave replication
binlog-checksum = CRC32 #效验码
sync-master-info = 1 #值为1确保信息不会丢失
sync_relay_log_info = 1
master-verify-checksum = 1 #启动主服务器效验
slave-sql-verify-checksum = 1 #启动从服务器效验
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
pid-file=/opt/mariadb10/data/mysql.pid
11)创建复制用户
mysql -uroot -p'mariadb'
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.168.%' IDENTIFIED BY 'replpass';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> show slave hosts; #查看从服务器连接状态
MariaDB [(none)]> show global status like "rpl%"; #查看客户端
12)数据库迁移
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK; #对所有数据库加只读锁
MariaDB [(none)]> SHOW MASTER STATUS; #查看binlog和pos值,记录File和Position的部分值
MariaDB [(none)]> SELECT BINLOG_GTID_POS("mysql-bin.00001", 1000); #使用上一步得到到binlog点位值计算GTID值
#mysqldump -uroot -p test > /opt/test.sql; #将到处的库倒入到从库
3.安装配置中继mariadb
1)my.cnf配置
#password = your_password
port = 3389
socket = /opt/mariadb10/data/mysql.sock
# Here follows entries for some specific programs
# The MariaDB server
[mysqld]
port = 3389
socket = /opt/mariadb10/data/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
innodb_file_per_table = on
skip_name_resolve = on
expire_logs_days = 30
max_connections = 3000
log_slave_updates = on
read_only = on
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 2 #服务器CPU核数*2
# binary logging is required for replication
log-bin = mysql-bin
replication-ignore-db = mysql #设定需要忽略的复制数据库 (多数据库使用逗号,隔开)
# binary logging format - mixed recommended
binlog_format = mixed
# but will not function as a master if omitted
server-id = 2 #此处权制大于masterdb
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_buffer_pool_size = 4G #设置为占内存的70%-80%,如服务器16G内存设置值为4G
innodb_flush_method = O_DIRECT
#output different types of log information
general_log = on
general_log_file = /var/log/mariadb/general.log
log_error = /var/log/mariadb/error.log
slow_query_log = on
slow_query_log_file = /var/log/mariadb/slow_query.log
long_query_time = 1
#GTID master to slave replication
relay-log = relay-bin
slave-parallel-threads = 2 #同时启动多少个复制线程,最多与要复制的数据库数量相等即可
binlog-checksum = CRC32
sync-master-info = 1
sync_relay_log_info = 1
master-verify-checksum = 1
slave-sql-verify-checksum = 1
relay_log_recovery = 1
log-slave-updates = 1
relay-log-index = relay-bin
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
pid-file=/opt/mariadb10/data/mysql.pid
2)启用主从复制a.使用GTID方式
MariaDB [(none)]> SET GLOBAL gtid_slave_pos = "0-1-51"; #主DB得到到GTID值
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.168.10',MASTER_PORT = 3389,MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_USE_GTID=slave_pos; #从授权
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUS\G
MariaDB [(none)]> show variables like '%relay%'; #从服务器Slave查看relay的所有相关参数
如果Slave_IO_Running与Slave_SQL_Running都为YES,则从服务已运行无问题,在主DB执行:
MariaDB [(none)]> unlock tables #解锁数据表
MariaDB [(none)]> FLUSH PRIVILEGES;
b.mysql5.6以后版本
CHANGE MASTER TO MASTER_HOST='192.168.168.10', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_AUTO_POSITION=1;
c.普通方式(需要主库锁库记录bin-log和pos值)
CHANGE MASTER TO MASTER_HOST='192.168.168.10',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=1174;
注:如果报 Could not initialize master info structure for '';执行
reset slave;
FLUSH PRIVILEGES;
3)创建中继用户
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'relayuser'@'172.16.216.%' IDENTIFIED BY 'relaypass';
MariaDB [(none)]> FLUSH PRIVILEGES;
4)查看中继从master GTID值
MariaDB [(none)]> SHOW MASTER STATUS; #查看binlog和pos值,记录File和Position的部分值
MariaDB [(none)]> SELECT BINLOG_GTID_POS("mysql-bin.000053", 358); #使用上一步得到到binlog点位值计算GTID值
4.安装配置远程从mariadb
1)my.cnf配置
[client]
#password = your_password
port = 3389
socket = /opt/mariadb10/data/mysql.sock
# Here follows entries for some specific programs
# The MariaDB server
[mysqld]
port = 3389
socket = /opt/mariadb10/data/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
innodb_file_per_table = on
skip_name_resolve = on
expire_logs_days = 30
max_connections = 3000
sync_binlog = 1
log_slave_updates = on
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 2 #服务器CPU核数*2
# binary logging is required for replication
log-bin = mysql-bin
replication-ignore-db = mysql #设定需要忽略的复制数据库 (多数据库使用逗号,隔开)
# binary logging format - mixed recommended
binlog_format = mixed
# but will not function as a master if omitted
server-id = 3 #此处权限值配置大于中继从
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_buffer_pool_size = 4G #设置为占内存的70%-80%,如服务器16G内存设置值为4G
innodb_flush_method = O_DIRECT
#output different types of log information
general_log = on
general_log_file = /var/log/mariadb/general.log
log_error = /var/log/mariadb/error.log
slow_query_log = on
slow_query_log_file = /var/log/mariadb/slow_query.log
long_query_time = 1
#GTID master to slave replication
slave-parallel-threads = 2
binlog-checksum = CRC32
sync-master-info = 1
sync_relay_log_info = 1
master-verify-checksum = 1
slave-sql-verify-checksum = 1
log-slave-updates = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
pid-file=/opt/mariadb10/data/mysql.pid
2)启用主从复制
MariaDB [(none)]> SET GLOBAL gtid_slave_pos = "0-1-80"; #中继DB得到的master的GTID值,此步可省略
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.216.12',MASTER_PORT = 3389,MASTER_USER='relayuser',MASTER_PASSWORD='relaypass',MASTER_USE_GTID=slave_pos;
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUS\G
注:不锁表中从同步1.先建立好主从关系
2.在新从库执行stop slave;
3.从slave1从库中用如下命令导出预同步的库
mysqldump -uroot -p'mariadb' --skip-lock-tables --master-data=1 -B {database1 database2} > /opt/{database}.sql
4.将copy至新的从库slave2并导入
mysql -uroot -p'mariadb' < /opt/{database}.sql
5.在slave2执行start slave;
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。