mysql/mariadb

配置文件：

        MySQL有多个配置文件，会去找各个配置文件，后面的配置会覆盖前面的配置，/etc/my.cnf --> /etc/mysql/my.cnf --> $MYSQL_HOME/my.cnf --> --default-extra-file=/path/to/somedir/my.cnf --> ~/.my.cnf。

安装后的设定：

1、为所有root用户设定密码：

        mysql> set password for 'root'@'localhost' = PASSWORD('新密码');

        mysql> update mysql.user set password=PASSWORD('新密码') where user='root'；

        # mysqladmin -u root -p password '新密码'         回车后输入原来密码。 

2、删除所有匿名用户：

        mysql> drop user ''@'localhost';

        上述两个步骤可以使用命令：mysql_secure_installation 直接运行这个命令，这是个mysql初始化工具，根据提示可以删除匿名账户，修改root密码，禁止root远程登录等等操作。

3、关闭主机名反解功能；可能会依赖于DNS拖慢响应速度。

MySQL工具：

客户端工具：

        mysqldump：数据备份操作命令，将数据转换为SQL语句到文本。

                mysqldump -u root -p password  tablename > backup.sql

        mysqladmin：基于mysql协议管理mysqld。

        mysqlimport：数据导入工具。

        mysql -uroot -p123456 database_name < xxx.sql

非客户端管理工具：

        myisamchk，myisampack

获取程序默认使用的配置：

        mysql --print-defaults

        mysqld --print-defaults

客户端类应用程序的可用选项：

        -u ，--user=

        -h，--host=

        -p，--password=

        -P，--port=

        --protocol=

        -S，--socket=

        -D，--database=

        -C，--compress

        mysql -uroot -p123456 -e "show databases";   可以直接运行sql语句。

获取运行中数据库的参数和值：

        mysql> show global variables\G;

        mysql> show session variables\G;

修改服务器变量的值：

        全局：

        mysql> set global system_var_name=value;

        mysql> set @@global.system_vat_name=value;

        会话：

        mysql> set [session] system_var_name=value;

        mysql> set @@[session.]system_var_name=value;

状态变量：用于保存mysqld运行中的统计数据的变量：

        mysql> show GLOBAL STATUS;        可以看到各语句运行的次数。

        mysql> show [session] STATUS;

显示字符集和排序规则：

        show character set;

        show collactions;

sql mode 定义mysqld对约束的响应行为：

修改方式：

        set global sql_mode='MODE';    不加global时仅对修改后创建的会话有效。常用的mode有 traditional，strict_trans_tables，strict_all_tables。比如说当模式为 traditional 时插入的数据长度超过设定的长度时会返回错误，不允许插入这条数据，但是是默认情况下的处理是将超长的部分剪切掉再插入。

索引类型：

        B+索引、hash索引、R树索引、fulltext索引

        聚集索引、非聚集索引

        简单索引、组合索引

创建表：

        1、直接创建

        2、查询现存的表来创建

        3、通过复制现存的表结构创建，不复制数据。

查看表状态：

        show table status like 'user'\G;        以字节为单位。

单进程多线程：

        每个用户连接都会创建单独的连接线程，通过这个线程去响应客户端请求，Mysql是单进程的，所以必须要维护一个线程池来管理众多线程。

查询：

查询去重：distinct

select distinct field_name from table_name;

分析语句：explain

explain select * from test;

联合查询：union

将查询的结果拥有相同字段的列联合成一张表：

select name,age from student  union  select name,age from teachers;

用户授权

创建用户：

create user 'username'@'host' [identified by 'password'];

查看授权：

show grant for 'username'@'host';

重命名用户：

rename user oldname to newname;

删除用户：

drop user 'username'@'host';

修改密码：

set password for 'username'@'host';

mysqladmin password

mysql命令行工具：

mysqladmin --help

创建数据库：

mysqladmin -uroot -p -h create db1;

删除数据库：

mysqladmin -u -p -h drop db1;

清空计数：

flush status;

找回mysql账号密码：

1、启动进程时使用 --skip-grant-tables 跳过授权表，--skip-networking 禁止远程登录

2、使用update命令修改管理员密码所在表，update mysql.user set password=PASSWORD('password') where user='root';

3、重启mysql服务

授权：grant

取消授权：revoke

查询缓存

        查询缓存空间不必过大，集群状态下可能会成为瓶颈。通过查询语句的哈希值来判断是否命中缓存。有些查询是不会被缓存的，如临时表，自定义变量，包含UDF等情况。

与缓存功能相关的服务器变量：

SHOW GLOBAL VARIABLES LIKE 'query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_strip_comments   | OFF      |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+

query_cache_limit: MySQL能够缓存的最大查询结果；如果某查询的结果大于此值，则不会被缓存；

query_cache_min_res_unit: 查询缓存中分配内存的最小单位；

计算公式：（query_cache_size-Qcache_free_memory）/Qcache_queries_in_cache

query_cache_size: 查询缓存的总体可用空间；其必须为1024的倍数；

query_cache_type: 

query_cache_wlock_invalidate：当其它会话锁定此次查询用到资源时，是否不能再从缓存中返回数据；

与缓存相关的状态变量：

SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16759656 |
| Qcache_hits             | 16       |
| Qcache_inserts          | 71       |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 57       |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+

Qcache_hits/Com_select

衡量缓存是否足够有效的另一种思路：Qcache_hits/Qcache_inserts

如果此比值大于3:1, 说明缓存也是有效的；如果高于10:1，相当理想；

缓存优化的思路：

1、批量写入比单次写入对缓存的影响要小得多；

2、缓存空间不宜过大，大量缓存的同时失效会导致MySQL假死；

3、必要时，使用SQL_CACHE或SQL_NO_CACHE手动控制缓存；

4、对写密集型的应用场景，禁用缓存反而能提高性能；

碎片整理：FLUSH QUERY_CACHE

清空缓存：RESET QUERY_CACHE

索引

        索引应该构建在作为查询条件的字段上。索引也可以建立在多个字段上。

索引类型：

        B+ Tree索引：顺序索引，每一个叶子到根节点的距离是相同的，左前缀索引，适合查询范围类的数据。

        Hash索引：基于hash表的实现，适合精确匹配索引中的所有列。

mysql5.5之后默认的存储引擎是InnoDB：

Inn

处理大量的短期事务：

数据存储于“表空间”table space 中。

InnoDB表：

1、所有InnoDB表的数据和索引放置与同一个表空间中。

        表空间文件：datadir定义的目录下，ibdata1、ibdata2

2、每个表单独使用一个表空间存储表的数据和索引。

        innodb_file_per_table=NO

MyISAM表：

支持全文索引，压缩、空间函数、不支持事务、表级锁，奔溃后无法安全恢复。

适用场景：读多写少。

文件：

        tb1_name.frm：表格式定义

        tb1_name.MYD：数据文件

        tb1_name.MYI：索引文件

其他存储引擎：

        CSV：将普通的CSV作为MYSQL表使用。

        MRG_MYISAM：将多个MyISAM表合并为一张虚拟表

        BLACKHOLD：类似于/dev/null，不存储任何数据

        MEMORY：数据存储于内存中，支持hash索引，表级锁，断电数据消失，临时表

        PERFORMANCE_SCHEMA：伪存储引擎

        ARCHIVE：归档存储引擎，只支持SELECT和INSERT

        FEDERATED：代理，将查询发给其他远程MySQL

MariaDB：支持的其他存储引擎：

        OQGraph：

        SphinxSE：全文搜索引擎

        ToukDB：

        Cassandra：

        Connect：

        SQUENCE：

要想使用其他存储引擎就要编译安装来支持。

并发控制

锁：

        读锁：共享锁

        写锁：独占锁

锁粒度：

        表级锁

        行级锁

手动加锁，如： LOCK TABLES user READ;

解锁：UNLOCK TABLES user;

事务

关于事务的几个变量：

        innodb_log_file_size

        innodb_log_files_in_group

        innodb_log_group_home_dir

        事务是有事务日志的，当进行事务处理时，将执行的结果先存入日志中，然后存入数据库。事务日志是一段连续的磁盘空间，避免直接存入数据库而产生的大量随机IO，随机IO的处理时间比较长，在这段时间中MySQL可能会遇到断电宕机等意外情况导致数据丢失。写入文件速度较快，写完日志之后再将数据写入数据库，降低数据丢失的风险。

日志文件

查询日志：query log 默认不开启

慢查询日志：slow query log

错误日志：error log

二进制日志：binary log

中继日志：reley log

事务日志：transaction log

使用如下命令查看日志：

show global variables like '%log%';

查询日志：

        general_log=ON|OFF        是否开启日志

        general_log_file=hostname.log        指定日志名称

        log_output=TABLE|FILE|NONE        日志输出位置

慢查询日志：

        执行时长超出指定时长的查询操作。

        long_query_time        慢查询时长

        set global long_query_time=        设置慢查询时长，单位秒

        slow_query_log=ON|OFF        是否开启慢查询日志

        slow_query_log_file=hostname-slow.log        日志文件名称

        log_slow_filter=admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk        日志记录模式

        log_slow_queries=OFF        是否记录慢查询日志，同slow_query_log

        log_slow_rate_limit=1        日志记录速率，每秒记录个数

        log_slow_verbosity            日志记录详细级别

错误日志：

        错误日志并不会记录所有错误，会记录多种错误信息，包括mysql的启动关闭所产生的时间也会记录。错误日志默认是不开启的，但是在centos7上的mariadb错误日志是开启的。

        mysqld启动活关闭过程中的事件信息。

        mysqld运行中产生的错误信息。

        event scheduler运行一个event时产生的日志信息。

        在主从复制架构中的从服务器上启动从服务器线程时产生的信息。

        log_error=/var/log/mariadb/mariadb.log|ON|OFF        指定日志文件，当使用ON时使用默认路劲

        log_warnings=1|0        是否记录警告信息

二进制日志：

        记录数据改变或潜在导致数据改变的SQL语句，用来生成数据副本。

show binary logs;        查看二进制日志

mysql> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000355 | 1073741974 |
| mysql-bin.000356 | 1073742175 |
+------------------+------------+

show master logs;        查看master日志状态

mysql> show master logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000355 | 1073741974 |
| mysql-bin.000356 | 1073742175 |
+------------------+------------+

二进制日志是滚动的，mysql每重启一次二进制日志后缀数字就增加1。

show master status;    查看当前使用的二进制日志

mysql> show master status;
+------------------+------------+--------------+------------------+-------------------+
| File             | Position   | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+------------+--------------+------------------+-------------------+
| mysql-bin.000379 | 1001231788 |              |                  |                   |
+------------------+------------+--------------+------------------+-------------------+

查看日志事件

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

二进制日志格式：

        语句记录：记录执行的语句 statement

        行记录：记录执行的数据结果 row

        混合模式：让系统自行判断 mixed

二进制日志的构成：

        日志文件：mysql-bin.000001，二进制格式

        索引文件：mysql-bin.index，文本格式

服务器变量：

        sql_log_bin=ON|OFF        是否记录二进制日志

        log_bin=/path/to/bin_log_file        记录的文件位置

        binlog_format=statement|row|mixed    二进制日志记录的格式

        max_binlog_size        单个二进制日志的最大体积

                1、到达最大值会自动滚动

                2、日志的大小未必是指定的值，因为单个事件不能够存放在两个日志中。

        max_binlog_stmt_cache_size        缓存二进制日志的语句，二进制日志先写入到内存中再保存到磁盘

        expire_logs_days        日志的过期时长

        sync_binlog=1|0        二进制日志的同步功能。1为同步日志，2为异步

二进制日志的格式：

        mysqlbinlog 命令可用来查看二进制日志，也可以度其他主机上的二进制日志

                --start-position

                --stop-position

                --start-datetime=

                --stop-datetime=YYYY-MM-DD hh:mm:ss

mysqlbinlog master-bin.000001

#180612  5:09:35 server id 1  end_log_pos 245 Start: binlog v 4, server v 5.5.56-MariaDB created 180612  5:09:35 at startup

事件，服务器标识，事件结束位置，事件类型，事件所在服务器事件线程，写入时间差，错误代码，事件内容

GTID：global transaction ID;        每个事件的事务的ID号，避免冲突

开启二进制日志：

        sql_log_bin=ON

        log_bin=ON

中继日志：

        复制架构中，从服务器读取主服务器二进制的事件的日志，relay_log

事务日志：

        transaction log 事务型存储引擎自行管理和使用。事务日志文件是一个组，循环将日志写入到数据库，然后腾空此日志，再将下一个日志文件写入到数据库。

        innodb_log_group_home_dir        事务日志文件位置

        innodb_log_files_in_group            每个组有多少个文件

        innodb_log_file_size                      每个文件的大小

        事务日志默认位置：/var/lib/mysql/ib_logfile0  、ib_logfile0

        还有两个跟事务相关的日志，redo log 重做日志，undo log 撤销日志

MySQL查看实时执行的SQL语句：

        开启这项功能非常消耗性能，所以最好临时开启，看完后关闭。

查看是否已经开启实时SQL语句记录。

mysql> show variables like 'general_log%';
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | OFF                          |
| general_log_file | /data/mysql/prd-mysql001.log |
+------------------+------------------------------+

临时开启：

SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general_log.log';

永久开启的方法：

在my.cnf配置文件中加入如下内容，然后重启。

general_log = 1
general_log_file = /var/log/mysql/general_sql.log

备份和恢复

备份类型：

        完全备份：备份整个数据集

        部分备份：只备份部分

        增量备份：从之前备份后的点开始接着备份

        差异备份：备份与之前备份不同的部分

        热备份：备份时读写操作均可执行，InnoDB支持热备

        温备份：备份时只能进行读操作，MyISAM支持温备

        冷备份：读写操作均不可进行

        物理备份：直接复制数据文件进行备份，直接复制文件，占用空间大，恢复方便，直接复制数据即可。热备相对困难。

        逻辑备份：导出数据另存进行备份，基于协议使用客户端读取数据，以文本方式备份，丢失数据精度，且与存储引擎无关，恢复数据缓慢，热备相对容易。

        逻辑备份或物理备份都可以热备或冷备份，与备份方式无关。

备份内容：

        数据，二进制日志，InnoDB的事务日志。

备份方案：

        数据集：完全+增量

        备份手段：物理，逻辑

        总之根据实际情况进行备份。

备份工具：

        mysqldump：逻辑备份工具，可温备，完全或部分备份，InnoDB热备。

        cp，tar等冷备份工具：可完全备份，部分备份。

        lvm2的快照：几乎热备，施加全局读锁的方式不能读写，然后立即快照，再释放锁。

备份工具选择：

        mysqldump+复制binlog：使用mysqldump进行完全备份，使用binlog复制指定时间范围的event进行增量备份。

        lvm2快照+复制binlog：复制lvm快照，使用binlog复制指定时间范围的event进行增量备份。

        xtrabackup：对InnoDB做物理热备的工具，支持完全备份增量备份。

备份和恢复：

逻辑备份工具：mysqldump、mydumper、phpMyAdmin

mysqldump 

        --all-databases/-A     备份所有数据库

        -B db_name/--databases db_name    备份指定数据库

        锁表方式：

        --lock-all-tables/-x    锁定所有库中的所有表

        --lock-tables/-l          对于每个单独的数据库上锁，在启动备份之前锁定其所有表

        对InnoDB表一样生效，实现温备。

InnoDB：支持热备：

        --single-transaction

其他选项：

        -E，--events：备份指定数据库相关的所有event scheduler

        -R，--routines：备份指定数据库相关的所有存储过程和存储函数

        --triggers：备份相关的触发器

完全备份：

        完全备份+二进制日志的方式：在完全备份时要记录二进制文件的文件名和事件日志，要启动binlog日志。

记录二进制日志位置的方法：

        --master-data=1/2

                1：备份时保存 change master to 语句，此语句不被注释。

                2：备份时保存 change master to 语句，但是是被注释的。

        --flush-logs：

                锁定表完成后执行flush logs命令，保证日志进行滚动。

        注意：二进制日志不应该与数据文件放在同一磁盘中。

先进行完全备份：

mysqldump -uroot --all-databases --lock-all-tables --master-data=2 > /root/xxx.sql

打开备份的数据中在前几十行中可以看到如下这行：

CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000005', MASTER_LOG_POS=245;

然后从binlog日志中恢复出增量数据：

mysqlbinlog --start-position=245 /var/lib/mysql/master-bin.000005 > delta.sql

恢复还原：

        先将binlog关闭再将两个sql语句按照顺序导入即可：

mysql -uroot -p < backup.sql
mysql -uroot -p < delta.sql

基于lvm2的备份：

        首先数据要在lvm2上，再创建lvm逻辑券，挂载逻辑券，再将数据库中的数据目录切换到逻辑券下。

请求锁定所有表：

flush tables with read lock;

滚动二进制日志：

flush logs;

记录二进制日志文件位置及事件位置：

show master status;

因为要记录这个二进制文件的位置所以可以使用如下命令来保存：

mysql -e 'show master status' > position.txt

创建逻辑券快照：

lvcreate -L 500M -n mydata-snap-name -p r -s /dev/myvg/mydata

快照创建完成后解除读锁：

unlock tables;

然后挂载快照进行备份：

mount -r /dev/myvg/mydata-snap-name /mnt

进入到挂载的快照中就可以进行备份了：

cd /mnt/
cp -a mysql/ /tmp

备份好数据后可以删除快照。

恢复数据时将数据复制到数据目录即可：

cp -a /tmp/mysql/* /data/mysql

复制回来的数据数组属主要确保是mysql的，但是要想将数据恢复到某个时刻就要重放二进制数据了。

查看备份之前保存的二进制文件的位置：

cat position.txt

重放二进制文件：

mysqlbinlog --start-position=245 mysql-bin.000004

这样即可恢复数据到某个点了，注意备份二进制要重原卷中备份。

xtrabackup：

        第三方组织提供的备份工具。

下载软件：

下载地址：https://www.percona.com/downloads/XtraBackup/LATEST/

        在这个下载页面中选择版本和平台，下面会出现下载地址：在 Download Packages Separately 一栏中有三个下载项，一个是debug用的，一个是test用的，这里直接下载正式的包即可，选择下面的 percona-xtrabackup-24-2.4.11-1.el7.x86_64.rpm 。

安装percona-xtrabackup：

安装时候可能会依赖到epel yum源中的包，所以要事先安装epel yum 源。

yum install percona-xtrabackup-24-2.4.11-1.el7.x86_64.rpm

安装后可查看：

rpm -ql percona-xtrabackup

        xtrabackup是以客户端协议连接至MySQL服务端进行备份的，将增量备份放在主干分支上进行还原的，会读取 /etc/my.cnf 配置文件中的 [mysql] 和 [xtrabackup] ，也就是说可以在MySQL的配置文件中配置xtrabackup 的相关配置。如设置备份文件放置的目录：

target_dir = /data/backup/mysql/

备份方法：

xtrabackup --backup --datadir=/var/data/mysql/ --target-dir=/data/backup/mysql

完全备份：

innobackupex --user=username --password=password /path/to/backup-dir

        innobackupex 是 xtrabackup 的封装，使其使用格式与Oracle一样。xtrabackup 的更多使用命令详见官方手册。

主从复制

主从配置过程：

        主节点：

                1、启动二进制日志

                2、为当前节点设置一个全局的ID号

                3、创建有复制权限的账号

        从节点：

                1、启动中继日志

                2、为当前节点设置一个全局唯一的ID好

                3、使用有复制权限的账号连接至主服务器，并启动复制线程

复制架构中应该注意的问题：

        1、限制从服务器为只读

                在从服务器上设置 read_only=ON; 此限制对拥有super权限的用户均无效。

                阻止所有用户修改操作可以启动一个客户端运行 flush tables with read lock; 这个客户端就不关闭了。

        2、如何保证主从复制的事务安全？

                在master节点启用参数：

                        sync_binlog=ON

                如果用到的为InnoDB存储引擎：

                        innodb_flush_logs_at_trx_commit=ON

                        innodb_support_xa=ON

                在slave节点：

                        skip_slave_start=ON

                master节点：

                        sync_master_info

                slave节点：

                        sync_relay_log

                        sync_relay_log_info

主主复制

互为主从：

        1、数据不一致，因此要慎用

        2、自动增涨ID

                配置一个节点使用奇数ID：

                        auto_increment_offset=1

                        auto_increment_increment=2

                另一个节点使用偶数ID：

                        auto_increment_offset=2

                        auto_increment_increment=2

配置步骤：

        1、各节点使用一个唯一server_id

        2、都启用 binary log 和 relay log

        3、创建拥有复制权限的用户账号

        4、定义自动增长id字段的数值范围为奇数偶数

        5、均把对方指定为主节点，并启动复制线程

MHA：对主从复制结构做高可用

        MHA（Master HA）是一款开源的MySQL的高可用程序，它为MySQL主从复制架构提供了automating master failover 功能。MHA在监控到 master 节点故障时，会提升其中拥有最新数据的 slave 节点成为新的 master 节点。

Galera Cluster：提供数据复制功能的组件

        Galera Cluster 是一套基于行的多主模型复制集群的开源解决方案，能保证数据不丢失。