1.MySQL存储引擎
总结：mysql常见的存储引擎以及特点。
#MySQL常用数据库引擎
1.InnoDB，MySQL5.5版之后默认的存储引擎
2.MyISAM，MySQL5.5版之前默认的存储引擎
#MyISAM和InnoDB区别
1.事务支持：MyISAM强调性能，但不提供事务支持；innoDB支持事务；
2.存储结构：
3.存储空间：
4.可移植性、备份及恢复：
5.AUTO_INCREMENT:
6.表锁差异：MyISAM只支持表级锁，InnoDB支持行锁。
7.全文索引：MyISAM支持全文索引，InnoDB不支持。
8.表主键：MyISAM允许没有任何索引和主键的表存在；InnoDB如果没有主键，会自动生成一个主键。
9.表的具体行数：MyISAM保存表的总行数，InnoDB不保存总行数，会遍历整个表，消耗较大。
10.外键：InnoDB支持，MyISAM不支持
11.查询效率：没有where的count(*)使用MyISAM要比InnoDB快得多。因为MyISAM内置了一个计数器，count(*)时它直接从计数器中读，而InnoDB必须扫描全表。
12.CURD操作：如果执行大量select，MyISAM较好；如果数据执行大量insert，update，InnoDB性能更好。
2.MySQL日志类型
总结：MySQL日志各种类型。
#MySQL日志类型：
# 1.事务日志transaction log：
顺序追加，事务日志ib_logfile0,ib_logfile1
redo log
undo log
相关配置：
mysql> show variables like ‘%innodb_log%‘;
日志性能优化：innodb_flush_log_at_trx_commit=0|1|2
# 2.错误日志errlog：
MySQL本身启动，停止，运行期间发生的错误信息。
mysql> show global variables like ‘log_error‘;
# 3.慢查询日志slow query log：
记录执行时间过长的sql，时间阈值可以配置，只记录执行成功。
相关变量：
slow_query_log=ON|OFF
long_query_time=N
slow_query_log_file=HOSTNAME-slow.log
query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
log_queries_not_using_indexes=ON
log_slow_rate_limit=1
log_slow_verbosity=query_plan,explain
log_slow_queries=OFF
分析工具:mysqldumpslow -s c -t 10 /mysql/slow.log
# 4.二进制日志binary log ：
记录导致数据改变或潜在导致数据改变的SQL语句
记录已提交的日志
不依赖与存储引擎类型
mysql> show variables like ‘%binlog_format%‘;
文件类型：
日志文件：mysql-bin.000001 二进制格式
索引文件：mysql-bin.index 文本格式
相关变量：
sql_log_bin=ON|OFF
log_bin=/path/bin_log_file
binlog_format=STATMENT|ROW|MIXED
max_binlog_size=103741824
binlog_cache_size=4m
max_binlog_cache_size=512m
sync_binlog=1|0
expire_logs_days=N
查看工具：mysqlbinlog,二进制日志的客户端命令工具，支持离线查看二进制日志
# 5.中继日志relay log：
在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件
# 6.普通日志general log：
通用设置：
general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE
3.MySQL主从复制
mysql主从复制架构原理：
主节点：
master服务器：数据更新，写入bin log ，启动slave服务线程同步
dump thread：为每个salve的IO thread启动一个dump线程，用于向其发送binary log events
从节点：
slave服务器：启动IO thread线程，写入replay log，执行SQL tread线程，数据更新
IO thread：向master请求二进制日志事件，并保存于中继日志中
SQL thread：从中继日志中读取日志事件，在本地完成重放
实践：MySQL主从复制及主主复制的实现。
#1.环境准备
操作系统：centos7.6
数据库：mysql5.6
IP规划：192.168.40.17（主数据库服务器）,192.168.40.117（从数据库服务器）
#2.主从复制
主节点配置
配置文件：/etc/my.cnf
1.启用二进制日志
[mysqld]
log_bin
2.为当前节点设置一个全局唯一的ID号
[mysqld]
server-id=33060
log-basename=master #可选，设置datadir日志名称，不依赖主机名
3.创建有复制权限的用户账号
mysql> grant replication slave on *.* to ‘repluser‘@‘192.168.40.17‘ identified by ‘replpass‘;
4.查看从二进制日志的文件和位置开始进行复制
mysql> show master log;
#3.从节点配置
1.启动中继日志
[msyqld]
server_id=33061 #为当前节点设置一个全局性唯一的ID号
log-bin
read_only=ON #设置数据库只读，针对supper user无效
replay_log=relay-log #replay log的文件路径，默认值hostname-replay-bin
replay_log_index=replay-log.index #默认值hostname-replay-bin.index
2.使用有复制权限的用户账号连接至主服务器，并启动复制线程
mysql> CHANGE MASTER TO MASTER_HOST=‘masterhost‘,
mysql> MASTER_USER=‘repluser‘,
mysql> MASTER_PASSWORD=‘replpass‘,
mysql> MASTER_LOG_FILE=‘mysql-bin.xxxxxx‘,
mysql> MASTER_LOG_POS=#;
START SLAVE [IO_THREAD|SQL_THREAD];
SHOW SLAVE STATUS;
实例：mysql主从复制配置：
主节点配置：
#/etc/my.cnf
[root@CentOS7 ~]# vim /etc/my.cnf
[mysqld]
server-id=33060
log_bin
[root@CentOS7 ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@CentOS7 ~]# mysql -uroot -p
Enter password:
mysql> grant replication slave on *.* to repluser@‘192.168.40.%‘ identified by ‘cms_medu‘;
Query OK, 0 rows affected, 1 warning (0.00 sec)
#查看二进制文件位置
mysql> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| CentOS7-bin.000001 | 452 |
+--------------------+-----------+
1 row in set (0.00 sec)
从节点配置：
#/etc/my.cnf
[root@CentOS7 ~]# vim /etc/my.cnf
[mysqld]
server-id=33061
[root@localhost ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@localhost ~]# mysql -uroot -p
Enter password:
mysql> help change master to
Name: ‘CHANGE MASTER TO‘
Description:
Syntax:
CHANGE MASTER TO option [, option] ...
option: {
MASTER_BIND = ‘interface_name‘
| MASTER_HOST = ‘host_name‘
| MASTER_USER = ‘user_name‘
| MASTER_PASSWORD = ‘password‘
| MASTER_PORT = port_num
| MASTER_CONNECT_RETRY = interval
| MASTER_RETRY_COUNT = count
| MASTER_DELAY = interval
| MASTER_HEARTBEAT_PERIOD = interval
| MASTER_LOG_FILE = ‘source_log_name‘
| MASTER_LOG_POS = source_log_pos
| MASTER_AUTO_POSITION = {0|1}
| RELAY_LOG_FILE = ‘relay_log_name‘
| RELAY_LOG_POS = relay_log_pos
| MASTER_SSL = {0|1}
| MASTER_SSL_CA = ‘ca_file_name‘
| MASTER_SSL_CAPATH = ‘ca_directory_name‘
| MASTER_SSL_CERT = ‘cert_file_name‘
| MASTER_SSL_CRL = ‘crl_file_name‘
| MASTER_SSL_CRLPATH = ‘crl_directory_name‘
| MASTER_SSL_KEY = ‘key_file_name‘
| MASTER_SSL_CIPHER = ‘cipher_list‘
| MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
| IGNORE_SERVER_IDS = (server_id_list)
}
server_id_list:
[server_id [, server_id] ... ]
CHANGE MASTER TO
RELAY_LOG_FILE=‘replica-relay-bin.006‘,
RELAY_LOG_POS=4025;
URL: https://dev.mysql.com/doc/refman/5.6/en/change-master-to.html
mysql> CHANGE MASTER TO MASTER_HOST=‘192.168.40.17‘,MASTER_USER=‘repluser‘,MASTER_PASSWORD=‘cms_medu‘,MASTER_PORT=3306,MASTER_LOG_FILE=‘mysql-bin.000001‘,MASTER_LOG_POS=452;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.40.17
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 452
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 452
Relay_Log_Space: 120
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file‘
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 33060
Master_UUID: a0d56ccf-b95f-11eb-97b7-000c2961a17a
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 210528 01:44:37
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
#解决1236错误
mysql> stop slave;
mysql> reset salve;
mysql> start salve;
4.数据备份恢复
xtrabackup工具特点：
1.备份还原过程快速、可靠
2.备份过程不会打断正在执行的事务
3.能够基于压缩等功能节约磁盘空间和流量
4.自动实现备份检验
5.开源、免费
备份还原数据步骤：
1.备份：对数据库做完全或增量备份
2.预准备：还原前，先对备份的数据，整理至一个临时目录。
3.还原：将整理好的数据，复制回数据库目录中
xtrabackup备份用法：
innobackupex [option] BACKUP-ROOT-DIR
#选项说明：
--user: #备份数据库的账号
--password: #备份数据库的密码
--host: #备份数据库的主机地址
--databases: #接受数据库名
--defaults-file: #指定从哪个文件读取mysql配置
--incremental: #创建一个增量备份
--incremental-basedir： #前一次全备份或增量备份的目录
--incremental-dir: #还原时增量备份的目录
--include=name #表名。格式dbname.tblname
还原用法：
innobackupex --copy-back [option] BACKUP-DIR
innobackupex --move-back [option] [--defaults-group=GROUP-NAME] BACKUP-DIR
#选项说明：
--copy-back： #数据恢复时，将备份数据文件拷贝到mysql服务器的datadir
--move-back: #不拷贝文件，移动文件到目的地
--force-non-empty-directories: #转移文件到非空目录，已存在文件不会被覆盖
实践：xtrabackup实现全量+增量+binlog恢复库。
#工具准备
[root@localhost ~]# yum -y install percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm
#1.执行全量备份
[root@localhost ~]# innobackupex --user=root --password=666666 /data/backup/all-db
#2.添加数据
mysql> insert into students(id,name) values(1,‘linux‘);
#3.执行首次增量备份
[root@localhost ~]# innobackupex --user=root --password=666666 --incremental /data/backup/incremental-1 --incremental-basedir=/data/backup/all-db
#4.添加数据
mysql> insert into students(id,name) values(2.‘python‘);
#5.执行第二次增量备份
[root@localhost ~]# innobackupex --user=root --password=666666 --incremental /data/backup/incremental-2 --incremental-basedir=/data/backup/incremental-1
#6.模拟删库跑路
[root@localhost ~]# rm -rf /var/local/mysql/*
[root@localhost ~]# rm -rf /var/local/mysql_logs/*
#7.预准备
[root@localhost ~]# innobackupex --apply-log --redo-only /data/backup/all-db/
[root@localhost ~]# innobackupex --apply-log --redo-only /data/backup/all-db/ --incremental-dir=/data/backup/incremental-1
[root@localhost ~]# innobackupex --apply-log --redo-only /data/backup/all-db/ --incremental-dir=/data/backup/incremental-2
[root@localhost ~]# innobackupex --apply-log /data/backup/all-db/
#8.恢复库
[root@localhost ~]# systemctl stop mysql.service
[root@localhost ~]# innobackupex --copy-back /data/backup/all-db/
[root@localhost ~]# chown -R mysql:mysql /var/local/mysql*
#9.检查
[root@localhost ~]# systemctl start mysql.service
mysql> SELECT * FROM whrdb.students;