储存引擎
MariaDB [hellodb]> show variables like 'innodb_file_per_table'; #查看索引是否在单独表中
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF |
+-----------------------+-------+
MariaDB [hellodb]> show variables like '%storage_engine%'; #列出默认的储存引擎
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
| storage_engine | InnoDB |
+------------------------+--------+
MariaDB [hellodb]> show variables; #列出所有变量
MariaDB [hellodb]> show variables like '%os'; #通配符，%代表任意字符
索引
MariaDB [hellodb]> explain select *from students where age>20; #查看是否利用索引
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | students | ALL | NULL | NULL | NULL | NULL | 25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
MariaDB [hellodb]> create index idx_age on students(age); #创建age索引
MariaDB [hellodb]> show indexes from students\G #查看表里所有索引
MariaDB [hellodb]> create index idx_name on students(name); #创建name索引
MariaDB [hellodb]> explain select * from students where name like "s%"; #查询s开头的人
MariaDB [hellodb]> drop index idx_age on students; #删除age索引
MariaDB [hellodb]> create index idx_name_age on students(name,age); #创建name age复合索引
MariaDB [hellodb]> explain select * from students where name like "s%"; #查询s开头的利用索引
+------+-------------+----------+-------+-----------------------+----------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+-----------------------+----------+---------+------+------+-----------------------+
| 1 | SIMPLE | students | range | idx_name,idx_name_age | idx_name | 152 | NULL | 4 | Using index condition |
+------+-------------+----------+-------+-----------------------+----------+---------+------+------+-----------------------+
MariaDB [hellodb]> explain select *from students where age=20; #没有利用索引
MariaDB [hellodb]> explain select *from students where name like 'R%' and age=20; #利用索引
MariaDB [hellodb]> explain select *from students where stuid >10; #利用索引
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | students | range | PRIMARY | PRIMARY | 4 | NULL | 15 | Using where |
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
MariaDB [hellodb]> explain select *from students where stuid+10 >20; #主键也是索引（不能用运算符，不能利用索引）
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | students | ALL | NULL | NULL | NULL | NULL | 25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
MariaDB [hellodb]> show index_statistics; #查看服务器端配置
[root@CentOS7 ~]# mysql hellodb <testlog.sql #创建表testlog，testlog.sql为本地上传
MariaDB [hellodb]> call sp_testlog; #导入数据
MariaDB [hellodb]> select * from testlog where name='wang99999'; #耗时长（没利用索引）
+-------+-----------+-------+
| id | name | age |
+-------+-----------+-------+
| 99999 | wang99999 | 99999 |
+-------+-----------+-------+
MariaDB [hellodb]> create index idx_name on testlog(name); #创建索引
MariaDB [hellodb]> select * from testlog where name='wang99999'; #耗时短（利用索引）
+-------+-----------+-------+
| id | name | age |
+-------+-----------+-------+
| 99999 | wang99999 | 99999 |
+-------+-----------+-------+
MariaDB [hellodb]> explain select * from testlog where name='wang99999'; #查看是否利用索引
MariaDB [hellodb]> create unique index uni_age on testlog(age); #创建唯一索引
MariaDB [hellodb]> show indexes from testlog; #显示所有索引
锁
MariaDB [hellodb]> lock tables students read; #对表加读锁（所有人只能读）
MariaDB [hellodb]> update students set classid=1 where stuid=25; #不能修改
MariaDB [hellodb]> show processlist; #显示当前进程
+----+------+-----------+---------+---------+------+-------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+------+-----------+---------+---------+------+-------+------------------+----------+
| 14 | root | localhost | hellodb | Query | 0 | NULL | show processlist | 0.000 |
+----+------+-----------+---------+---------+------+-------+------------------+----------+
MariaDB [hellodb]> kill 14; #杀死该进程
MariaDB [hellodb]> update students set classid=1 where stuid=25; #能够修改
MariaDB [hellodb]> select * from students where stuid=25; #查询修改
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 25 | Sun Dasheng | 100 | M | 1 | NULL |
+-------+-------------+-----+--------+---------+-----------+
MariaDB [hellodb]> lock table students write; #对表加写锁，只能自己查看修改，别人不能查看修改
MariaDB [hellodb]> unlock tables; #对所有表解锁
MariaDB [hellodb]> flush tables with read lock; #加实例锁，全局锁，读写都不能，一般备份前使用
事务
事务隔离级别：从上至下更加严格
READ UNCOMMITTED 可读取到未提交数据，产生脏读
READ COMMITTED 可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致
REPEATABLE READ 可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置
SERIALIZABILE 可串行化，未提交的读事务阻塞修改事务，或者未提交的修改事务阻塞读事务。导致并发性能差
MariaDB [hellodb]> select @@tx_isolation; #查询隔离机制
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
vim /etc/my.cnf #设置服务器端隔离机制
[mysqld]
transaction-isolation=SERIALIZABLE
日志
事务日志
事务日志：transaction log
MyISAM不支持 InnoDB支持
MariaDB [(none)]> show variables like '%innodb_log%'; #查询事务日志变量
+-------------------------------+------------+
| Variable_name | Value |
+-------------------------------+------------+
| innodb_log_block_size | 0 | 块大小
| innodb_log_buffer_size | 16777216 | 缓存大小
| innodb_log_file_size | 50331648 | 每个日志文件大小
| innodb_log_files_in_group | 2 | 日志组成员个数
| innodb_log_group_home_dir | ./ | 事务文件路径
+-------------------------------+------------+
[root@CentOS7 ~]# ls /data/mysql/ #日志文件
ib_logfile0 ib_logfile1
innodb_flush_log_at_trx_commit 默认为1
1默认情况下，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性
0提交时没有任何操作; 而是每秒执行一次日志缓冲区写入和刷新。 这样可以提供更好的性能，但服务器崩溃可能丢失最后一秒的事务
2每次提交后都会写入日志缓冲区，但每秒都会进行一次刷新。 性能比0略好一些，但操作系统或停电可能导致最后一秒的交易丢失
3模拟MariaDB 5.5组提交（每组提交3个同步），此项MariaDB 10.0支持
1造成硬盘IO频繁，磁盘性能降低，2能增加磁盘性能，但出问题会丢失数据
MariaDB [(none)]> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1 |
+--------------------------------+-------+
==事务日志一般存放于单独的高性能磁盘中或单独分区中，与数据分开存放
[root@CentOS7 ~]# cat /etc/my.cnf
[mysqld]
innodb_log_group_home_dir=/data/logs
错误日志
mysqld启动和关闭过程中输出的事件信息
mysqld运行中产生的错误信息
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'log_error'; #显示变量
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| log_error | /var/log/mariadb/mariadb.log |
+---------------+------------------------------+
[root@CentOS7 ~]# cat /etc/my.cnf #配置
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'log_warnings'; #是否记录警告信息至错误日志文件
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings | 2 |
+---------------+-------+
通用日志
记录对数据库的通用操作，包括错误的SQL语句
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'log_output'; #显示输出文件类型，可以选择TABLE|FILE|NONE
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'general_log%';
+------------------+-------------+
| Variable_name | Value |
+------------------+-------------+
| general_log | OFF | #关闭
| general_log_file | CentOS7.log | #文件名
+------------------+-------------+
慢查询
慢查询日志：记录执行查询时长超出指定时长的操作，一般用来检测SQL语句
MariaDB [(none)]> show variables like 'profiling'; 显示profiling设置
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
MariaDB [(none)]> set profiling=on; #设置为on
MariaDB [(none)]> show profiles; #显示时间
+----------+------------+----------------+
| Query_ID | Duration | Query |
+----------+------------+----------------+
| 1 | 0.00027574 | show profiling |
+----------+------------+----------------+
MariaDB [(none)]> show profile for query 2; #显示每个阶段时间
MariaDB [(none)]> show variables like 'long_query_time'; #慢查询的阀值，单位秒
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
MariaDB [(none)]> show variables like '%slow%';
+---------------------------+------------------------------------+
| Variable_name | Value |
+---------------------------+------------------------------------+
| log_slow_admin_statements | ON |
| log_slow_rate_limit | 1 | 多少次查询才记录，mariadb特有
| log_slow_slave_statements | ON |
| log_slow_verbosity | | 记录内容
| slow_launch_time | 2 |
| slow_query_log | OFF | 开启或关闭慢查询
| slow_query_log_file | CentOS7-slow.log | 慢查询日志文件
+---------------------------+------------------------------------+
二进制日志
不管支不支持事务都可以使用
记录导致数据改变或潜在导致数据改变的SQL语句
记录已提交的日志
不依赖于存储引擎类型
注意：建议二进制日志和数据文件分开存放
MariaDB [(none)]> show variables like 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON | 是否记录二进制日志，默认ON
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON | 指定文件位置，上述两项都开启才可用
+---------------+-------+
MariaDB [(none)]> set sql_log_bin=off; #临时禁用二进制日志
[root@CentOS7 ~]# ls /data/mysql #显示二进制文件，一般与数据分开存放
mysql-bin.000001 mysql-bin.000003 mysql-bin.000002 mysql-bin.000004
[root@CentOS7 ~]# cat /etc/my.cnf #设置分区存放
[mysqld]
log_bin=/data/logbin/mysql-bin
MariaDB [(none)]> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED | 二进制日志记录的格式（STATEMENT|ROW|MIXED）
+---------------+-------+
## ROW格式占空间，但更安全，STATEMENT记录的语句，语句含有now时出问题，要安全选择ROW，MIXED混合模式，系统选择
MariaDB [(none)]> show binary logs; #显示二进制文件大小
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 351 | #每次重启服务重新开一个文件
| mysql-bin.000002 | 351 |
| mysql-bin.000003 | 351 |
| mysql-bin.000004 | 328 |
+------------------+-----------+
4 rows in set (0.00 sec)
MariaDB [(none)]> show master logs; #显示二进制文件大小
MariaDB [(none)]> show master status; #显示正在使用的
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 328 | | |
+------------------+----------+--------------+------------------+
MariaDB [(none)]> show binlog events in 'mysql-bin.000004'; #查看日志记录的事件
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
| mysql-bin.000004 | 4 | Format_desc | 1 | 256 | Server ver: 10.2.25-MariaDB-log, Binlog ver: 4 |
| mysql-bin.000004 | 256 | Gtid_list | 1 | 285 | [] |
| mysql-bin.000004 | 285 | Binlog_checkpoint | 1 | 328 | mysql-bin.000004 |
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
MariaDB [(none)]> show binlog events in 'mysql-bin.000004' from 285; #查询285以后的
+------------------+-----+-------------------+-----------+-------------+------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------------+-----------+-------------+------------------+
| mysql-bin.000004 | 285 | Binlog_checkpoint | 1 | 328 | mysql-bin.000004 |
+------------------+-----+-------------------+-----------+-------------+------------------+
MariaDB [(none)]> show binlog events in 'mysql-bin.000004' from 285 limit 2,3; #查询285后跳过2个后面3个
mysqlbinlog
二进制日志的客户端命令工具
mysqlbinlog [OPTIONS] log_file…
–start-position=# 指定开始位置
–stop-position=#
–start-datetime=
–stop-datetime=
时间格式：YYYY-MM-DD hh:mm:ss
–base64-output[=name]
-v -vvv
[root@CentOS7 ~]# mysqlbinlog --start-position=285 mysql-bin.000004 -v #查看
[root@CentOS7 ~]# mysqlbinlog mysql-bin.000003 -v >/data/test.sql #导出文件，可以用来还原数据库
purge
清除指定二进制日志
PURGE BINARY LOGS TO ‘mariadb-bin.000003’;删除3之前的日志
备份与还原
完全备份：整个数据集
部分备份：只备份数据子集，如部分库或表
增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂
差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单
冷备：读写操作均不可进行
温备：读操作可执行；但写操作不可执行
热备：读写操作均可执行
MyISAM：温备，不支持热备
InnoDB：都支持
冷备份
[root@CentOS7 ~]# vim /etc/my.cnf
log_bin=/data/logbin/mysql-bin
停止服务，可以拔网线或者
FLUSH TABLES WITH READ LOCK给全库加锁
[root@CentOS7 ~]# tar Jcvf all.bak.tar.xz /var/lib/mysql #压缩备份数据库
[root@CentOS7 ~]# tar Jcvf logbin.tar.xz /data/logbin/ #压缩备份二进制日志
[root@CentOS7 ~]# mkdir /data/backup
[root@CentOS7 ~]# mv all.bak.tar.xz /data/backup/ #拷贝
[root@CentOS7 ~]# mv logbin.tar.xz /data/backup/
[root@CentOS7 ~]# cp /etc/my.cnf /data/backup/
[root@CentOS7 ~]# scp /data/backup/* 192.168.8.17:/data #传给其他主机
另一台主机192.168.8.17
安装软件
[root@CentOS7 ~]# cp /data/my.cnf /etc/my.cnf -b #配置文件修改，原配置文件备份
[root@CentOS7 ~]# mkdir /data/logbin #创建文件夹
[root@CentOS7 ~]# chown mysql.mysql /data/logbin #改用户、组
[root@CentOS7 ~]# tar xf /data/all.bak.tar.xz #解压
[root@CentOS7 ~]# tar xf /data/logbin.tar.xz
[root@CentOS7 ~]# mv data/logbin/* /data/logbin #拷贝
[root@CentOS7 ~]# mv var/lib/mysql/* /var/lib/mysql/ #拷贝
[root@CentOS7 ~]# systemctl start mariadb
热备份
mysqldump
删库备份还原
##二进制日志与数据分开存放
[root@CentOS7 ~]# mysqldump -A --master-data=2 >/data/all.sql #完整备份（2为注释，1为命令，一般用于主从复制）
[root@CentOS7 ~]# less /data/all.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=7655; #二进制文件的标记（注释）
MariaDB [hellodb]> insert students(name,age) values('a',30); #修改库
MariaDB [hellodb]> insert students(name,age) values('b',30);
MariaDB [hellodb]> insert students(name,age) values('c',40);
[root@CentOS7 ~]# rm -rf /var/lib/mysql/* #删除库
[root@CentOS7 ~]# systemctl restart mariadb #重启服务生成相关库文件，但没有表
MariaDB [(none)]> set sql_log_bin=off; #关闭二进制日志（否则导入时会生成二进制日志，与原日志冲突）
MariaDB [(none)]> flush tables with read lock; #加锁（备份用）
[root@CentOS7 /data/logbin]# mysqlbinlog --start-position=7655 mysql-bin.000003 >/data/inc.sql #备份二进制日志（从完整备份二进制日志标记点开始往后备份）
MariaDB [(none)]> source /data/all.sql #导入完整备份
MariaDB [test]> source /data/inc.sql #导入二进制日志（完整备份后产生的数据）
MariaDB [hellodb]> set sql_log_bin=on; #启用二进制日志
MariaDB [hellodb]> unlock tables; #解锁
删表备份还原
[root@CentOS7 /data/logbin]# mysqldump -A --master-data=2 > /data/all.`date +%F`.sql #完整备份
[root@CentOS7 /data/logbin]# less /data/all.2020-06-30.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=522331;
MariaDB [hellodb]> insert students(name,age) values('d',35);
MariaDB [hellodb]> insert students(name,age) values('e',35);
MariaDB [hellodb]> insert students(name,age) values('f',35);
MariaDB [hellodb]> drop table students; #删表
MariaDB [hellodb]> insert teachers(name,age) values('f',35);
MariaDB [hellodb]> insert teachers(name,age) values('e',35);
MariaDB [hellodb]> flush tables with read lock; #加锁
MariaDB [hellodb]> set sql_log_bin=off; #关闭二进制日志
[root@CentOS7 /data/logbin]# mysqlbinlog --start-position=522331 mysql-bin.000006 >/data/inc.sq #二进制日志备份
[root@CentOS7 /data/logbin]# vim /data/inc.sql #:/DROP查找到相关删表语句
#DROP TABLE `students` /* generated by server */ #注释掉删表的语句（否则运行会删除表）
[root@CentOS7 ~]# rm -rf /var/lib/mysql/* #删除库（最简单方法，反正要重新导入，删除完避免冲突）
[root@CentOS7 /data/logbin]# systemctl restart mariadb
MariaDB [hellodb]> source /data/all.2020-06-30.sql; #导入
MariaDB [hellodb]> source /data/inc.sql;
MariaDB [hellodb]> set sql_log_bin=on;
MariaDB [hellodb]> unlock tables;
[root@CentOS7 /data/logbin]# mysqldump -A -F --single-transaction --master-data=2 >/data/all.sql #完整备份（二进制日志会重新生成，避免后面修改新日志与以前的就日志在一个文件）
MariaDB [(none)]> purge binary logs to 'mysql-bin.000010'; #删除10以前的二进制日志
MariaDB [(none)]> reset master; #二进制日志重新从1开始计数（重置）
分库备份脚本
[root@CentOS7 /data/logbin]# for db in `mysql -e 'show databases' |grep -Ev '^(Database|information_schema|performance_schema)$'`;do mysqldump -B $db --single-transaction --master-data=2 |gzip >/data/$db.sql.gz ;done
分库备份脚本
[root@CentOS7 /data/logbin]# mysql -e 'show databases' |grep -Ev '^(Database|information_schema|performance_schema)$' |sed -rn 's#(.*)#mysqldump -B \1 --single-transaction --master-data=2 |gzip >/data/\1\.sql\.gz#p' |bash
InnoDB建议备份策略
mysqldump –uroot –A –F –E –R --single-transaction --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob >KaTeX parse error: Expected group after '_' at position 15: BACKUP/fullbak_̲BACKUP_TIME.sql
MyISAM建议备份策略
mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob >KaTeX parse error: Expected group after '_' at position 15: BACKUP/fullbak_̲BACKUP_TIME.sql
xtrabackup
完整备份
[root@CentOS7 ~]# yum install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm #安装软件
[root@CentOS7 ~]# xtrabackup --backup --target-dir=/data/backup #备份
[root@CentOS7 ~]# scp -r /data/backup/ 192.168.8.17:/data/backup
###预准备
[root@CentOS7 ~]# xtrabackup --prepare --target-dir=/data/backup
####如果有库需要删除，停止服务
[root@CentOS7 ~]# xtrabackup --copy-back --target-dir=/data/backup #复制到数据库目录
[root@CentOS7 ~]# chown -R mysql.mysql /var/lib/mysql #还原属性
[root@CentOS7 ~]# systemctl start mariadb
完整备份及增量备份
[root@CentOS7 ~]# xtrabackup --backup --target-dir=/data/backup #完全备份
MariaDB [hellodb]> insert teachers (name,age) values ('wang',20);
MariaDB [hellodb]> insert teachers (name,age) values ('a',20);
[root@CentOS7 ~]# xtrabackup --backup --target-dir=/data/inc1 --incremental-basedir=/data/backup #第一次增量备份
MariaDB [hellodb]> insert teachers (name,age) values ('zhang',30);
MariaDB [hellodb]> insert teachers (name,age) values ('zhb',30);
[root@CentOS7 ~]# xtrabackup --backup --target-dir=/data/inc2 --incremental-basedir=/data/inc1 ##第二次增量备份
[root@CentOS7 ~]# scp -r /data/ 192.168.8.17:/data
[root@CentOS7 ~]# systemctl stop mariadb #停止服务
[root@CentOS7 ~]# rm /var/lib/mysql/* -rf #删除文件
[root@CentOS7 ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/backup #预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
[root@CentOS7 ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/backup --incremental-dir=/data/inc1 #合并第1次增量备份到完全备份
[root@CentOS7 ~]# xtrabackup --prepare --target-dir=/data/backup --incremental-dir=/data/inc2 #合并第2次增量备份到完全备份
[root@CentOS7 ~]# xtrabackup --copy-back --target-dir=/data/backup #复制到数据库目录
[root@CentOS7 ~]# chown -R mysql.mysql /var/lib/mysql/
[root@CentOS7 ~]# systemctl start mariadb
主从复制
一主一从
[root@CentOS7 ~]# vim /etc/my.cnf #服务器配置
[mysqld]
server_id=1
log_bin=/data/logbin/mysql-bin
[root@CentOS7 ~]# mkdir /data/logbin
[root@CentOS7 ~]# chown mysql.mysql /data/logbin
[root@CentOS7 ~]# systemctl restart mariadb
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.8.%' identified by 'centos'; #创建账号
MariaDB [(none)]> show master logs; #显示二进制日志
+------------------+-----------+
| mysql-bin.000003 | 399 |
+------------------+-----------+
[root@CentOS7 ~]# vim /etc/my.cnf
[mysqld]
server_id=2
read_only
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.8.7',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='centos',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000003',
-> MASTER_LOG_POS=399;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
在现有mysql服务器上基础上，实现主从复制
[root@CentOS7 ~]# vim /etc/my.cnf #服务器配置
[mysqld]
server_id=1
log_bin=/data/logbin/mysql-bin
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.8.%' identified by 'centos'; #创建账号
[root@CentOS7 ~]# mysqldump -A --single-transaction --master-data=1 -F >/data/all.sql #完整备份（应该加锁）
[root@CentOS7 ~]# scp /data/all.sql 192.168.8.17:/data/
MariaDB [(none)]> show master logs; #显示二进制日志
+------------------+-----------+
| mysql-bin.000004 | 245 |
+------------------+-----------+
[root@CentOS7 ~]# vim /data/all.sql #使用有复制权限的用户账号连接至主服务器
CHANGE MASTER TO
MASTER_HOST='192.168.8.7',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=245;
[root@CentOS7 ~]# vim /etc/my.cnf #配置
[mysqld]
server_id=2
read_only
[root@CentOS7 ~]# mysql </data/all.sql #还原
MariaDB [(none)]> start slave; #启动复制线程
MariaDB [(none)]> show slave status\G
复制出现错误，忽略错误继续复制
从服务器
MariaDB [hellodb]> insert teachers(name,age) values ('a',20);
主服务器
MariaDB [hellodb]> insert teachers(name,age) values ('b',20);
从服务器
MariaDB [hellodb]> show slave status\G #报错
Last_SQL_Errno: 1062 #错误编号
Last_Error: Error 'Duplicate entry '5' for key 'PRIMARY''
##报错后，将不再复制
MariaDB [hellodb]> set global sql_slave_skip_counter=1; #方法一：跳过1个错误（1可以修改）
MariaDB [hellodb]> stop slave;
MariaDB [hellodb]> start slave;
MariaDB [hellodb]> show slave status\G #查看没有错误
[root@CentOS7 ~]# vim /etc/my.cnf #方法二：跳过同一种错误（改配置）
slave_skip_errors=1062
[root@CentOS7 ~]# systemctl restart mariadb
主服务器down，从服务器变为主服务器
再搭建一台从服务器
主服务器
[root@CentOS7 ~]# mysqldump -A --single-transaction --master-data=1 -F >/data/all.sql
[root@CentOS7 ~]# scp /data/all.sql 192.168.8.27:/data/
[root@CentOS7 ~]# vim /etc/my.cnf #配置
[mysqld]
server_id=3
read_only
[root@CentOS7 ~]# vim /data/all.sql
CHANGE MASTER TO
MASTER_HOST='192.168.8.7',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=245;
[root@CentOS7 ~]# mysql </data/all.sql
MariaDB [(none)]> start slave;
主服务器down，从服务器（二进制日志最新的）提升为主服务器
MariaDB [(none)]> show slave status\G
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 711 #以最新的为主服务器
MariaDB [(none)]> stop slave; #停止服务
MariaDB [(none)]> reset slave all; #清除所有从服务器上设置的主服务器同步信息
[root@CentOS7 ~]# vim /etc/my.cnf #启用二进制日志，解锁
log_bin=/data/logbin/mysql-bin
[root@CentOS7 ~]# mkdir /data/logbin
[root@CentOS7 ~]# chown mysql.mysql /data/logbin
MariaDB [(none)]> select user,host from mysql.user; #查看用于同步的用户
[root@CentOS7 ~]# systemctl restart mariadb #重启
MariaDB [(none)]> show master logs; #显示日志
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 245 |
+------------------+-----------+
##另一台从服务器
MariaDB [(none)]> stop slave; #停止服务
MariaDB [(none)]> reset slave all; #清除所有从服务器上设置的主服务器同步信息
MariaDB [(none)]> CHANGE MASTER TO #使用有复制权限的用户账号连接至主服务器
-> MASTER_HOST='192.168.8.17',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='centos',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=245;
MariaDB [(none)]> start slave; #启动进程
MariaDB [(none)]> show slave status\G
级联复制
主-------->从（级联）--------->从
主服务器
[root@CentOS7 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log_bin=/data/logbin/mysql-bin
[root@CentOS7 ~]# mkdir /data/logbin
[root@CentOS7 ~]# chown mysql.mysql /data/logbin
[root@CentOS7 ~]# systemctl start mariadb
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.8.%' identified by 'centos';
[root@CentOS7 ~]# mysqldump -A -F --single-transaction --master-data=1 >/data/all.sql
[root@CentOS7 ~]# scp /data/all.sql 192.168.8.17:/data
级联服务器
[root@CentOS7 ~]# vim /data/all.sql
CHANGE MASTER TO
MASTER_HOST='192.168.8.7',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=245;
[root@CentOS7 ~]# vim /etc/my.cnf
[mysqld]
server_id=2
log_bin=/data/logbin/mysql-bin
read_only
log_slave_updates
[root@CentOS7 ~]# mkdir /data/logbin
[root@CentOS7 ~]# chown mysql.mysql /data/logbin
[root@CentOS7 ~]# systemctl start mariadb
[root@CentOS7 ~]# mysql < /data/all.sql
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
[root@CentOS7 ~]# mysqldump -A -F --single-transaction --master-data=1 >/data/all.sql
[root@CentOS7 ~]# scp /data/all.sql 192.168.8.27:/data
MariaDB [(none)]> flush privileges;
[root@CentOS7 ~]# vim /data/all.sql
CHANGE MASTER TO
MASTER_HOST='192.168.8.17',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=245;
[root@CentOS7 ~]# vim /etc/my.cnf
[mysqld]
server_id=3
read_only
[root@CentOS7 ~]# systemctl start mariadb
[root@CentOS7 ~]# mysql </data/all.sql
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
主主复制
主（192.168.8.7）
[root@CentOS7 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log_bin=/data/logbin/mysql-bin
auto_increment_offset=1
auto_increment_increment=2
[root@CentOS7 ~]# mkdir /data/logbin
[root@CentOS7 ~]# chown mysql.mysql /data/logbin
[root@CentOS7 ~]# systemctl start mariadb
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.8.%' identified by 'centos';
[root@CentOS7 ~]# mysqldump -A -F --single-transaction --master-data=1 >/data/all.sql
[root@CentOS7 ~]# scp /data/all.sql 192.168.8.17:/data
主192.168.8.17
[root@CentOS7 ~]# vim /etc/my.cnf
[mysqld]
server_id=2
log_bin=/data/logbin/mysql-bin
auto_increment_offset=2
auto_increment_increment=2
[root@CentOS7 ~]# vim /data/all.sql
CHANGE MASTER TO
MASTER_HOST='192.168.8.7',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=245;
[root@CentOS7 ~]# systemctl restart mariadb
[root@CentOS7 ~]# mysql </data/all.sql
MariaDB [(none)]> start slave;
MariaDB [(none)]> show master logs;
+------------------+-----------+
| mysql-bin.000006 | 523783 |
+------------------+-----------+
主机192.168.8.7
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.37.17',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='centos',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mariadb-bin.000006', MASTER_LOG_POS=523783;
MariaDB [(none)]> start slave;
半同步复制
MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。
同步复制需要所有从服务器复制完毕
半同步复制只需要一个从服务器复制完即可
需要借助插件rpl_semi_sync_master实现
主服务器
[root@CentOS7 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log_bin=/data/logbin/mysql-bin
[root@CentOS7 ~]# mkdir /data/logbin
[root@CentOS7 ~]# chown mysql.mysql /data/logbin/
[root@CentOS7 ~]# systemctl start mariadb
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.8.%' identified by 'centos';
MariaDB [(none)]> show master logs;
+------------------+-----------+
| mysql-bin.000003 | 399 |
+------------------+-----------+
从服务器一
[root@CentOS7 ~]# vim /etc/my.cnf
[mysqld]
server_id=2
read_only
[root@CentOS7 ~]# systemctl start mariadb
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.8.7',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='centos',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=399;
MariaDB [(none)]> start slave;
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=1;
另一台从服务器
[root@CentOS7 ~]# vim /etc/my.cnf
[mysqld]
server_id=3
read_only
[root@CentOS7 ~]# systemctl start mariadb
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.8.7',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='centos',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=399;
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=1;
MariaDB [(none)]> start slave;
主服务器
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
MariaDB [(none)]> set global rpl_semi_sync_master_enabled=on;
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 | 毫秒
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%';
复制过滤器
让从节点仅复制指定的数据库，或指定数据库的指定表
两种实现方式：
一、主服务器仅向二进制日志中记录与特定数据库相关的事件
binlog-do-db = 数据库白名单列表，多个数据库需多行实现
binlog-ignore-db = 数据库黑名单列表
问题：基于二进制还原将无法实现；不建议使用
二、从服务器SQL_THREAD在relay log中的事件时，仅读取与特定数据库(特定表)相关的事件并应用于本地
主服务器配置过滤
[root@CentOS7 ~]# vim /etc/my.cnf
[mysqld]
server_id=2
log_bin=/data/logbin/mysql-bin
binlog-ignore-db=hellodb
不会产生二进制日志，基于二进制还原将无法实现；不建议使用
从服务器配置过滤
主服务器不配置
replicate_do_db= 指定复制库的白名单
replicate_ignore_db= 指定复制库黑名单
replicate_do_table= 指定复制表的白名单
replicate_ignore_table= 指定复制表的黑名单
replicate_wild_do_table= foo%.bar% 支持通配符
replicate_wild_ignore_table=
[root@CentOS7 ~]# vim /etc/my.cnf
[mysqld]
server_id=3
log_bin=/data/logbin/mysql-bin
eplicate_do_db=hellodb,db1 #只复制hellodb,db1
加密复制
主服务器配置证书
[root@CentOS7 ~]# mkdir /etc/my.cnf.d/ssl
[root@CentOS7 ~]# cd /etc/my.cnf.d/ssl/
[root@CentOS7 /etc/my.cnf.d/ssl]# openssl genrsa 2048 >cakey.pem
[root@CentOS7 /etc/my.cnf.d/ssl]# openssl req -new -x509 -key cakey.pem -out cacert.pem -days 365
[root@CentOS7 /etc/my.cnf.d/ssl]# openssl req -newkey rsa:1024 -days 365 -nodes -keyout master.key >master.csr
[root@CentOS7 /etc/my.cnf.d/ssl]# openssl x509 -req -in master.csr -days 365 -CA cacert.pem -CAkey cakey.pem -set_serial 01 >master.crt
[root@CentOS7 /etc/my.cnf.d/ssl]# openssl req -newkey rsa:1024 -days 365 -nodes -keyout slave.key >slave.csr
[root@CentOS7 /etc/my.cnf.d/ssl]# openssl x509 -req -in slave.csr -days 365 -CA cacert.pem -CAkey cakey.pem -set_serial 02 >slave.crt
[root@CentOS7 /etc/my.cnf.d/ssl]# ls
cacert.pem cakey.pem master.crt master.csr master.key slave.crt slave.csr slave.key
[root@CentOS7 /etc/my.cnf.d/ssl]# vim /etc/my.cnf
[mysqld]
server_id=2
log_bin=/data/logbin/mysql-bin
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/master.crt
ssl-key=/etc/my.cnf.d/ssl/master.key
[root@CentOS7 /etc/my.cnf.d/ssl]# systemctl restart mariadb
MariaDB [(none)]> show variables like '%ssl%';
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.8.%' identified by 'centos' require ssl;
[root@CentOS7 ~]# scp -r /etc/my.cnf.d/ssl/ 192.168.8.7:/etc/my.cnf.d/ssl/
从服务器
######第一种方法
[mysqld]
server_id=1
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/slave.crt
ssl-key=/etc/my.cnf.d/ssl/slave.key
MariaDB [(none)]> stop slave;
MariaDB [(none)]> reset slave all;
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.8.17',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='centos',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=441,
-> MASTER_SSL=1;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
######第二种方法
[mysqld]
server_id=1
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.8.17',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='centos',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=441,
-> MASTER_SSL=1,
-> MASTER_SSL_CA = '/etc/my.cnf.d/ssl/cacert.pem',
-> MASTER_SSL_CERT = '/etc/my.cnf.d/ssl/slave.crt',
-> MASTER_SSL_KEY = '/etc/my.cnf.d/ssl/slave.key';
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
CTID（二进制日志标记不指定，自动商量）
二进制安装mysql-5.7.26-el7-x86_64
[root@CentOS7 ~]# tar xvf mysql-5.7.26-el7-x86_64.tar.gz -C /usr/local/
[root@CentOS7 ~]# cd /usr/local/
[root@CentOS7 /usr/local]# ln -s mysql-5.7.26-el7-x86_64/ mysql #也可直接改名
[root@CentOS7 /usr/local]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@CentOS7 /usr/local]# . /etc/profile.d/mysql.sh
[root@CentOS7 /usr/local]# useradd -r -s /sbin/nologin mysql
[root@CentOS7 /usr/local]# mysqld --initialize --user=mysql --datadir=/data/mysql
2020-07-02T20:16:47.553681Z 1 [Note] A temporary password is generated for root@localhost: ar6cbrfo4h(D
[root@CentOS7 /usr/local]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
[root@CentOS7 /usr/local]# cp mysql/support-files/mysql.server /etc/init.d/mysqld
[root@CentOS7 /usr/local]# chkconfig --add mysqld
[root@CentOS7 /usr/local]# service mysqld start
[root@CentOS7 /usr/local]# mysqladmin -uroot -p'jed8V#pm#=Hj' password centos
[root@CentOS7 /usr/local]# mysql -pcentos
主服务器配置
[root@CentOS7 /usr/local]# vim /etc/my.cnf
[mysqld]
server-id=1
log-bin=/data/logbin/mysql-bin
gtid_mode=ON
enforce_gtid_consistency
[root@CentOS7 /usr/local]# mkdir /data/logbin
[root@CentOS7 /usr/local]# chown mysql.mysql /data/logbin/
[root@CentOS7 /usr/local]# service mysqld restart
[root@CentOS7 /usr/local]# mysql -pcentos
mysql> grant replication slave on *.* to repluser@'192.168.8.%' identified by 'magedu';
从服务器配置
[root@CentOS7 /usr/local]# vim /etc/my.cnf
[mysqld]
server-id=2
gtid_mode=ON
enforce_gtid_consistency
[root@CentOS7 /usr/local]# service mysqld restart
[root@CentOS7 /usr/local]# mysql -pcentos
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.8.7',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='magedu',
-> MASTER_PORT=3306,
-> MASTER_AUTO_POSITION=1;
mysql> start slave;
ProxySQL实现读写分离
前端ProxySQL软件安装
[root@CentOS7 ~]# cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
> [proxysql_repo]
> name= ProxySQL YUM repository
> baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
> gpgcheck=1
> gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
> EOF
[root@CentOS7 ~]# yum install proxysql
主从复制使用CTiD环境
配置从服务器
[root@CentOS7 /usr/local]# vim /etc/my.cnf
read-only #从服务器必加（ProxySQL靠read-only区分主从服务器）
[root@CentOS7 /usr/local]# service mysqld restart
前端ProxySQL配置
[root@CentOS7 ~]# yum install mariadb -y
[root@CentOS7 ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
MySQL [(none)]> show databases;
MySQL [(none)]> show tables;
MySQL [(none)]> select * from sqlite_master where name='mysql_servers'\G
MySQL [(none)]> select * from mysql_servers;
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.8.7',3306);
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.8.27',3306);
MySQL [(none)]> load mysql servers to runtime;
MySQL [(none)]> save mysql servers to disk;
MySQL [(none)]> select * from mysql_servers;
master节点
mysql> grant replication client on *.* to monitor@'192.168.8.%' identified by 'magedu';
前端ProxySQL配置
MySQL [(none)]> set mysql-monitor_username='monitor';
MySQL [(none)]> set mysql-monitor_password='magedu';
MySQL [(none)]> load mysql variables to runtime;
MySQL [(none)]> save mysql variables to disk;
MySQL [(none)]> select * from mysql_server_connect_log;
MySQL [(none)]> select * from mysql_server_ping_log;
MySQL [(none)]> insert into mysql_replication_hostgroups values(10,20,"test");
MySQL [(none)]> load mysql servers to runtime;
MySQL [(none)]> save mysql servers to disk;
MySQL [(none)]> select hostgroup_id,hostname,port,status,weight from mysql_servers;
master节点
mysql> grant all on *.* to sqluser@'192.168.8.%' identified by 'magedu';
前端ProxySQL配置
MySQL [(none)]> insert into mysql_users(username,password,default_hostgroup) values('sqluser','magedu',10);
MySQL [(none)]> load mysql users to runtime;
MySQL [(none)]> save mysql users to disk;
[root@CentOS7 ~]# mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'create database testdb'
[root@CentOS7 ~]# mysql -usqluser -pmagedu testdb -P6033 -h127.0.0.1 -e 'create table t(id int)'
[root@CentOS7 ~]# mysql -usqlmysql -uadmin -padmin -P6032 -h127.0.0.1
MySQL [(none)]> insert into mysql_query_rules
-> (rule_id,active,match_digest,destination_hostgroup,apply)VALUES
-> (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1);
MySQL [(none)]> load mysql query rules to runtime;
MySQL [(none)]> save mysql query rules to disk;
MHA高可用
对主节点进行监控，可实现自动故障转移至其它从节点；通过提升某一从节点为新的主节点，基于主从复制实现，还需要客户端配合实现，目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库
主从复制已搭建好（master192.168.8.17，slave192.168.8.27、37）
主从节点都要复制账号
master主机
[root@CentOS7 ~]# vim /etc/my.cnf
[mysqld]
server-id=17
log-bin
skip_name_resolve=1
MariaDB [(none)]> grant all on *.* to mhauser@'192.168.8.%' identified by 'centos';
salve主机
[root@CentOS7 ~]# vim /etc/my.cnf
[mysqld]
server-id=27
read-only
relay_log_purge=0
skip_name_resolve=1
[root@CentOS7 ~]# vim /etc/my.cnf
[mysqld]
server-id=37
read-only
relay_log_purge=0
skip_name_resolve=1
管理机
[root@CentOS7 ~]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm
[root@CentOS7 ~]# yum install mha4mysql-manager-0.56-0.el6.noarch.rpm
[root@CentOS7 ~]# mkdir /etc/mastermha
[root@CentOS7 ~]# vim /etc/mastermha/app1.cnf
[server default]
user=mhauser
password=centos
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=repluser
repl_password=centos
ping_interval=1
[server1]
hostname=192.168.8.17
candidate_master=1
[server2]
hostname=192.168.8.27
candidate_master=1
[server3]
hostname=192.168.8.37
[root@CentOS7 ~]# ssh-keygen -t rsa -P "" -f /root/.ssh/id_rsa
[root@CentOS7 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 127.0.0.1
[root@CentOS7 ~]# scp -r /root/.ssh 192.168.8.17:/root/
[root@CentOS7 ~]# scp -r /root/.ssh 192.168.8.27:/root/
[root@CentOS7 ~]# scp -r /root/.ssh 192.168.8.37:/root/
###检测配置是否错误
[root@CentOS7 ~]# masterha_check_ssh --conf=/etc/mastermha/app1.cnf
[root@CentOS7 ~]# masterha_check_repl --conf=/etc/mastermha/app1.cnf
###启动
[root@CentOS7 ~]# masterha_manager --conf=/etc/mastermha/app1.cnf
##开启后只使用一次，当master节点坏后关闭，所有节点配置好后重新配置启动
Galera Cluster
针对主主复制，
都至少需要三个节点，不能安装mariadb-server
了解即可，大量写入时性能差(通过事务方式较快)
980
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
