环境说明(见最下方注解可不锁表)：
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
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 STATUSG 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 STATUSG
注：不锁表中从同步
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;