Mysql

Mysql

数据库字符集设置

yum install mariadb-server mariadb mariabd-libs -y
systemctl start mariadb
mysql
MariaDB [(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 mariadb
MariaDB [(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]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 1
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (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 mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> update user set password=password('1') where user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> q
Bye
pkill mariadb
systemctl start mariadb
mysql -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服务的ID
auto_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数量×2
thread_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=3306
socket=/tmp/mysql.sock.
[mysqld]
user=mysq1
server_id=10
port=3306
socket=/tmp/mysql.sock datadir=/data/mysql/
old_ passwords=1
lower_case_table_names=1
character-set-server=utf8
default-storage-engine=MYISAM
log-bin=bin.log
log-error=error.log pid-file=mysql.pid
long_query_time=2
slow_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=30
ft_min_word_len=4
back_1og=512
max allowed packet =64MB max_connections=4096
max connect errors=100
join_ buffer_size=2MB
read buffer size=2MB
read rnd buffer_size=2MB
sort buffer_size=2MB
query_cache_size=64MB
table_open_cache=10000
thread_cache_size=256
max heap table size =64MB
tmp_table_size=64MB
thread_stack=192KB
thread_concurrency=24
local-infile=0
skip-show-database
skip-name-resolve
skip-external-locking
connect_timeout=600
interactive timeout=600
wait_timeout=600
#***MYISAM
key_buffer_size=512MB
bulk_insert_buffer_size=64MB
myisam_sort_buffer_size=64MB
myisam_max_sort_file_size=1GB
myisam_repair_threads=1
concurrent_insert=2
myisam_recover
#***INNODB
innodb_buffer_pool_size=64GB
innodb additional_mem_pool_size=32MB
innodb data_file_path=ibdatal:1G;ibdata2:1G:autoextend
innodb read io_threads=8
innodb write io threads=8
innodb file_per_table=1
innodb flush log at_trx commit=2
innodb lock wait timeout =120
innodb log buffer size=8MBinnodb_log_file_size=256MB innodb_log files_in_group=3
innodb_max dirty_pages_pct=90
innodb_thread_concurrency=16
innodb_open_files=10000
#innodb_ force_recovery=4
#***Replication Slave read-only
#skip-slave-start relay-log=relay.1og
1og-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=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-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=on
log-slow-queries=/var/log/mariadb/mysql.log
long_query_time=0.01
log-queries-not-using-indexes
server-id=10
log-bin=mysql-bin

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-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=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-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=on
log-slow-queries=/var/log/mariadb/mysql.log
long_query_time=0.01
log-queries-not-using-indexes
server-id=20
#log-bin=mysql-bin

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-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 statusG
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

验证

master
create database test233;
slave
show 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 statusG

2、重新做主从同步，使数据完全同步。
此种方法适用于主从库数据内容相差很大的情况。
master端执行如下命令，将数据库设置全局读锁，不允许写人新数据。

flush tables with read lock；

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

unlock tables;