Mysql

数据库字符集设置

yum install mariadb-server mariadb mariabd-libs -ysystemctl start mariadbmysqlMariaDB [(none)]> show variables like '%char%';

方法一

• vim /etc/my.cnf
• [client]字段里加入：default-character-set=utf8
• [mysqld]字段里加入：character-set-server=utf8
• [mysql]字段里加入：default-character-set=utf8

方法二

show variables like'% char%';SET character _set_client=utf8; SET character_set_results =utf8; SET character_set_connection=utf8;

systemctl restart mariadbMariaDB [(none)]> show variables like '%char%';+--------------------------+----------------------------+| Variable_name            | Value                      |+--------------------------+----------------------------+| character_set_client     | utf8                       || character_set_connection | utf8                       || character_set_database   | utf8                       || character_set_filesystem | binary                     || character_set_results    | utf8                       || character_set_server     | utf8                       || character_set_system     | utf8                       || character_sets_dir       | /usr/share/mysql/charsets/ |+--------------------------+----------------------------+8 rows in set (0.00 sec)

数据库密码管理

1、创建用户及授权

• 授权localhost主机通过admin用户和pass密码访问本地的final库的所有权限
• 授权所有主机通过admin用户和admin密码访问本地的fina库的查询、插入、更新、删除权限
• 授权192.168.10.240主机通过admin用户和pass密码访问本地的jfedu库的所有权限

MariaDB [(none)]> create database final;Query OK, 1 row affected (0.00 sec)MariaDB [(none)]> grant all on final.* to admin@localhost identified by 'pass';Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> grant select,insert,update,delete on final.* to admin@"%" identified by 'admin';Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> grant all on final.* to admin@'192.168.10.254' identified by 'pass'; Query OK, 0 rows affected (0.00 sec)

2、密码破解方法

[root@Final pub]# systemctl stop mariadb[root@Final pub]# /usr/bin/mysqld_safe --user=mysql --skip-grant-tables &[1] 9560[root@Final pub]# 190516 13:28:29 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.190516 13:28:29 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql[root@Final pub]# mysqlWelcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 1Server version: 5.5.60-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> use mysqlReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedMariaDB [mysql]> update user set password=password('1') where user='root';Query OK, 4 rows affected (0.00 sec)Rows matched: 4  Changed: 4  Warnings: 0MariaDB [mysql]> flush privileges;Query OK, 0 rows affected (0.00 sec)MariaDB [mysql]> \qByepkill mariadbsystemctl start mariadbmysql -uroot -p

数据库配置文件详解

[mysqld]：服务器端配置datadir=/data/mysql：数据目录socket=/var/lib/mysql/mysql.sock:socket通信设置user=mysql：使用MySQL用户启动symbolic-links=0：是否支持快捷方式log-bin=mysql-bin：开启bin-log日志server-id=1：MySQL服务的IDauto_increment_offset=1：自增长字段从固定数开始auto_increment_increment=2：自增长字段每次递增的量socket=/tmp/mysql.sock:MySQL客户程序与服务器之间的本地通信套接字文件port=3306：指定MySQL监听的端口key_buffer=384MB:key_buffer是用于索引块的缓冲区大小table_cache=512：为所有线程打开表的数量sort_buffer_size=2MB：为每个需要进行排序的线程分配该大小的一个缓冲区read_buffer_size=2MB：读查询操作所能使用的缓冲区大小query_cache_size=32MB：指定MySQL查询结果缓冲区的大小read_rnd_buffer_size=8MB：改参数在使用行指针排序之后，随机读myisam_sort_buffer_size=64MB:MyISAM表发生变化时重新排序所需的缓冲thread_concurrency=8：最大并发线程数，取值为服务器逻辑CPU数量×2thread_cache=8：缓存可重用的线程数skip-locking：避免MySQL的外部锁定，减少出错几率增强稳定性default-storage-engine=INNODB：设置MySQL默认引擎为InnoDB#mysqld_safe config:MySQL服务安全配置[mysqld_safe]：MySQL服务安全启动配置log-error=/var/log/mysqld.log:MySQL错误日志路径pid-file=/var/run/mysqld/mysqld.pid:MySQL PID进程文件key_buffer_size=2048MB:MyISAM表索引缓冲区的大小max_connections=3000：MySQL最大连接数innodb_buffer_pool_size=2048MB:InnoDB内存缓冲数据和索引大小basedir=/usr/local/mysql55/：数据库安装路径[mysqldump]：数据库导出段配置max_allowed_packet=16MB：服务器和客户端发送的最大数据包

数据库索引

• 普通索引：normal，使用最广泛
• 唯一索引：unique，不允许重复的索引，允许有空值
• 全文索引：fulltext，只能用于MyISAM表，full text主要用于大量的内容检索
• 主键索引：primary key又称为特殊的唯一索引，不允许有空值
• 组合索引：为提高MySQL效率可建立组合索引。

MySQL数据库表创建各个索引命令，以t1表为案例，操作如下：主键索引：ALTER TABLE t1 ADD PRIMARY KEY（'column'）。唯一索引：ALTER TABLE t1 ADD UNIQUE（'column'）。普通索引：ALTER TABLE t1 ADD INDEXindex_name（'column'）。全文索引：ALTER TABLE t1 ADD FULLTEXT（'column'）。组合索引：ALTER TABLE t1 ADD INDEX index_name（'column1'，'column2'，column3'）。

MySQL数据库索引的缺点如下：

• MySQL数据库索引虽然能够提高数据库查询速度，但同时会降低更新、删除、插入表的速度，例如对表进行insert、update、delete时，update表MySQL不仅要保存数据，还需保存更新索引；
• 建立索引会占用磁盘空间，大表上创建了多种组合索引，索引文件就会占用大量的空间。

数据库慢查询

show variables like "%slow%";

show variables like "%long_query%";

• log_slow_queries：关闭慢查询日志功能
• long_query_time：慢查询超时时间，默认为10s，MySQL5.5以上可以设置微秒
• slow_query_log：关闭慢查询日志
• slow_query_log_file：慢查询日志文件
• slow_launch_time:thread create时间，单位为秒，如果thread create的时间超过了这个值，该变量slow_launch_time的值会加1

MariaDB [(none)]> show variables like "%slow%";+---------------------+--------------------------------------------------------------------------------------------------------------+| Variable_name       | Value                                                                                                        |+---------------------+--------------------------------------------------------------------------------------------------------------+| 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                                                                                                          || log_slow_rate_limit | 1                                                                                                            || log_slow_verbosity  ||| slow_launch_time    | 2                                                                                                            || slow_query_log      | OFF                                                                                                          || slow_query_log_file | Final-slow.log                                                                                               |+---------------------+--------------------------------------------------------------------------------------------------------------+7 rows in set (0.00 sec)MariaDB [(none)]> show variables like "%long_query%";+-----------------+-----------+| Variable_name   | Value     |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+1 row in set (0.00 sec)

开启慢查询日志

方法一

set global slow_query_log=on;

show variables like"%slow%";

方法二

在 /etc/my.cnf文件中[mysqld]下添加

log-slow-queries=/var/log/mariadb/mysql.log

long_query_time=0.01

log-queries-not-using-indexes

执行命令mysqldumpslow -h可以查看命令帮助信息，主要参数包括-s和-t，其中-s是排序参数

• l：查询锁的总时间
• r：返回记录数
• t：查询总时间排序
• al：平均锁定时间
• ar：平均返回记录数
• at：平均查询时间
• c：计数
• t n：显示头n条记录

MySQL慢查询mysqldumpslow按照返回的行数从大到小，查看前2行

mysqldumpslow -s r -t 2 /var/log/mariadb/mysql.log

MySQL慢查询mysqldumpslow按照查询总时间从大到小，查看前5行，同时过滤select的SQL语句

mysqldumpslow -s t -t 5 -g /var/log/mariadb/mysql.log

数据库优化

MySQL数据库优化是一项非常重要的工作，而且是一项长期的工作，MySQL优化三分靠配置文件及硬件资源的优化，七分靠SQL语句的优化。

MySQL数据库具体优化包括：配置文件的优化、SQL语句的优化、表结构的优化、索引的优化，而配置的优化包括：系统内核、硬件资源、内存、CPU、MySQL本身配置文件的优化。

硬件上的优化有两种方式：

• 一种是增加内存和提高磁盘读写速度，进而提高MySQL数据库的查询、更新的速度
• 另一种提高MySQL性能的方式是使用多块磁盘来存储数据，可以从多块磁盘上并行读取数据，进而提高读取数据的速度

MySQL参数的优化，内存中会为MySQL保留部分的缓冲区，这些缓冲区可以提高MySQL的速度，缓冲区的大小可以在MySQL的配置文件中进行设置。

以下为企业级MySQL百万量级真实环境配置文件my.cnf的内容，用户可以根据实际情况修改，代码如下：

摘

[client]port=3306socket=/tmp/mysql.sock.[mysqld]user=mysq1server_id=10port=3306socket=/tmp/mysql.sock datadir=/data/mysql/old_ passwords=1lower_case_table_names=1character-set-server=utf8default-storage-engine=MYISAMlog-bin=bin.loglog-error=error.log pid-file=mysql.pidlong_query_time=2slow_query_1og slow_query_log_file=slow.1og binlog_cache_size=4MB binlog_format=mixed maxbinlog_cache_size=16MB max binlog size=1GBexpire_logs_days=30ft_min_word_len=4back_1og=512max allowed packet =64MB max_connections=4096max connect errors=100join_ buffer_size=2MBread buffer size=2MBread rnd buffer_size=2MBsort buffer_size=2MBquery_cache_size=64MBtable_open_cache=10000thread_cache_size=256max heap table size =64MBtmp_table_size=64MBthread_stack=192KBthread_concurrency=24local-infile=0skip-show-databaseskip-name-resolveskip-external-lockingconnect_timeout=600interactive timeout=600wait_timeout=600#***MYISAMkey_buffer_size=512MBbulk_insert_buffer_size=64MBmyisam_sort_buffer_size=64MBmyisam_max_sort_file_size=1GBmyisam_repair_threads=1concurrent_insert=2myisam_recover#***INNODBinnodb_buffer_pool_size=64GBinnodb additional_mem_pool_size=32MBinnodb data_file_path=ibdatal:1G;ibdata2:1G:autoextendinnodb read io_threads=8innodb write io threads=8innodb file_per_table=1innodb flush log at_trx commit=2innodb lock wait timeout =120innodb log buffer size=8MBinnodb_log_file_size=256MB innodb_log files_in_group=3innodb_max dirty_pages_pct=90innodb_thread_concurrency=16innodb_open_files=10000#innodb_ force_recovery=4#***Replication Slave read-only#skip-slave-start relay-log=relay.1og1og-slave-updates

数据库集群

随着访问量的不断增加，单台MySQL数据库服务器压力不断地增加，需要对MySQL进行优化和架构改造，如果MyQSL优化不能明显改善压力，可以使用高可用、主从复制、读写分离来、拆分库、拆分表等方法来进行优化。

MySQL主从复制集群至少需要2台数据库服务器，其中一台为master库，另外一台为slave库，MySQL主从数据同步是一个异步复制的过程，要实现复制首先需要在master上开启bin-log日志功能，bin-log日志用于记录在master库中执行的增、删、修改、更新操作的SQL语句，整个过程需要开启3个线程，分别是master开启I/O线程，slave开启I/O线程和SQL线程，具体主从同步原理详解如下：

• slave 上执行slave start，slave I/O线程会通过在master创建的授权用户连接上至master，并请求master从指定的文件和位置之后发送bin-log日志内容
• master接收到来自slave I/O线程的请求后，master I/O线程根据slave发送的指定bin-log日志position点之后的内容，然后返回给slave的I/O线程
• 返回的信息中除了bin-log日志内容外，还有master最新的bin-log文件名以及在bin-log中的下一个指定更新position点
• slaveI/O线程接收到信息后，将接收到的日志内容依次添加到slave端的relay-log文件的最末端，并将读取到的master端的bin-log的文件名和position点记录到master.info文件中，以便在下一次读取的时候能告知master从相应的bin-log文件名及最后一个position点开始发起请求
• slave SQL线程检测到relay-log中内容有更新，会立刻解析relay-log日志中的内容，将解析后的SQL语句在slave里执行，执行成功后slave库与master库数据保持一致。

主从复制

master

[root@Final pub]# vim /etc/my.cnf[mysqld]character-set-server=utf8datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# Settings user and group are ignored when systemd is used.# If you need to run mysqld under a different user or group,# customize your systemd unit file for mariadb according to the# instructions in http://fedoraproject.org/wiki/Systemd#log_slow_queries=onlog-slow-queries=/var/log/mariadb/mysql.loglong_query_time=0.01log-queries-not-using-indexesserver-id=10log-bin=mysql-bin[mysqld_safe]log-error=/var/log/mariadb/mariadb.logpid-file=/var/run/mariadb/mariadb.pid[mysql]default-character-set=uft8[client]default-character-set=utf8## include all files from the config directory#!includedir /etc/my.cnf.d

MariaDB [(none)]> grant replication slave on *.* to 'mysqlsync'@'%' identified by 'pass';Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> show master status;+------------------+----------+--------------+------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000001 |      245 |              |                  |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)

slave

[root@Final pub]# vim /etc/my.cnf[mysqld]character-set-server=utf8datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# Settings user and group are ignored when systemd is used.# If you need to run mysqld under a different user or group,# customize your systemd unit file for mariadb according to the# instructions in http://fedoraproject.org/wiki/Systemd#log_slow_queries=onlog-slow-queries=/var/log/mariadb/mysql.loglong_query_time=0.01log-queries-not-using-indexesserver-id=20#log-bin=mysql-bin[mysqld_safe]log-error=/var/log/mariadb/mariadb.logpid-file=/var/run/mariadb/mariadb.pid[mysql]default-character-set=uft8[client]default-character-set=utf8## include all files from the config directory#!includedir /etc/my.cnf.d

MariaDB [(none)]> change master to master_host='192.168.10.254',master_user='mysqlsync',master_password='1',master_log_file='mysql-bin.000001',master_log_pos=245;Query OK, 0 rows affected (0.05 sec)MariaDB [(none)]> slave start;Query OK, 0 rows affected (0.02 sec)MariaDB [(none)]> show slave status\G             Slave_IO_Running: Yes            Slave_SQL_Running: Yes

验证

mastercreate database test233;slaveshow databases;master drop database test233;slave show databases;

主从同步排错

MySQL主从同步集群在生产环境使用时，如果主从服务器之间网络通信条件差或者数据库数据量非常大，容易导致MySQL主从同步延迟。

MySQL主从产生延迟之后，一旦主库宕机，会导致部分数据没有及时同步至从库，重新启动主库，会导致从库与主库同步错误，快速恢复主从同步关系有如下两种方法：

1、忽略错误后，继续同步

此种方法适用于主从库数据内容相差不大的情况。

master端执行如下命令，将数据库设置为全局读锁，不允许写入新数据。

flush tables with read lock;

slave 端停止slave I/O及SQL线程，同时将同步错误的SQL跳过1次，跳过错误会导致数据不一致，启动 start slave，同步状态恢复，命令如下：

stop slave;set global sql_slave_skip_counter=1;start slave;show slave status\G

2、重新做主从同步，使数据完全同步。

此种方法适用于主从库数据内容相差很大的情况。

master端执行如下命令，将数据库设置全局读锁，不允许写人新数据。

flush tables with read lock；

master端基于mysqldump、xtrabackup工具对数据库进行完整备份，也可以用shell脚本或python脚本实现定时备份，备份成功之后，将完整的数据导入至从库，重新配置主从关系，当slave端的I/O线程、SQL线程均为Yes之后，最后将master端读锁解开即可，解锁命令如下：

unlock tables;