DB-MySQL-Summary

发表于 更新于 分类于 Waline：

常用命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#连接mysql
mysql -u root -p
show status like 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 8     | #连接缓存数
| Threads_connected | 81    | #打开的连接数,如果该连接数大于max_connections当前最大连接数会报错
| Threads_created   | 181   | #创建过的线程数
| Threads_running   | 1     | #正在运行的连接数
+-------------------+-------+
show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   | #当前设置的最大连接数
+-----------------+-------+
 show processlist; #显示前100条的连接，如果显示所有show full processlist;
+------+------+------------------+------+---------+------+----------+------------------+
| Id   | User | Host             | db   | Command | Time | State    | Info             |
+------+------+------------------+------+---------+------+----------+------------------+
| 1100 | root | localhost        | NULL | Query   |    0 | starting | show processlist |
| 1147 | root | 10.255.0.2:52580 | xhzg | Sleep   | 7226 |          | NULL             |
+------+------+------------------+------+---------+------+----------+------------------+

mysql配置my.cnf，添加挂在卷 - /dockerdata/manager/mysqldata/config:/etc/mysql/conf.d

然后在挂在卷创建配置文件，添加配置my.cnf 文件名字随便

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
skip-name-resolve
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=/usr/local/mysql
# 设置mysql数据库的数据的存放目录
datadir=/usr/local/mysql/data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
lower_case_table_names=1
max_allowed_packet=16M
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

备份与恢复

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#备份数据库 manage geoserver两个
mysqldump -uroot -plfadmin --databases manage geoserver > ~/manageandgeoserver20180829.sql
# 恢复
mysql -uroot -plfadmin <manageandgeoserver20180829.sql
# 检查校验,进入mysql命令行
mysql -u root -p
# 显示所有数据库
mysql>  show databases;
# 使用 manage数据库
mysql> use manage;
# 显示所有用表
mysql> show tables;
#远程连接
mysql>  mysql -h172.16.16.8 -P14036 -uroot -p

参考 MySql数据库备份与恢复——使用mysqldump 导入与导出方法总结

主从库

  1. 修改主从配置库的配置文件

主库配置：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
[mysqld]
init_connect='SET NAMES utf8'
character-set-server=utf8
#无效屏蔽
# 设置mysql数据库的数据的存放目录
#datadir=/data/app/mysqldata/master/
#socket=/data/app/mysqldata/master/mysql.sock
#user=mysql
#port=3306

#Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-bin=mysql-bin
server-id=1
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-do-db=shenqics
#无效屏蔽
#[mysqld_safe]
#log-error=/data/app/mysqldata/master/mysqld.log
#pid-file=/data/app/mysqldata/master/mysqld.pid

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

从数据库配置：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
[mysqld]
init_connect='SET NAMES utf8'
character-set-server=utf8
#无效屏蔽
#datadir=/data/app/mysqldata/slave/
#socket=/data/app/mysqldata/slave/mysql.sock
#user=mysql
#port=3307

#Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-bin=mysql-bin
server-id=2
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
#只会复制shenqics该数据库，其他不会
replicate-do-db=shenqics
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60

#无效屏蔽
#[mysqld_safe]
#log-error=/data/app/mysqldata/slave/mysqld.log
#pid-file=/data/app/mysqldata/slave/mysqld.pid

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

  1. 启动服务

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    mysql-master:
       restart: always
       image: mysql:5.7.18
       environment:
         MYSQL_ROOT_PASSWORD: admin
       volumes:
         - /data/v-yinfu/mysql/master/data:/var/lib/mysql
         - /data/v-yinfu/mysql/master/config:/etc/mysql/conf.d
       ports:
         - target: 3306
           published: 14036
           protocol: tcp
           mode: host   
       deploy:
         replicas: 1
         restart_policy:
           condition: on-failure
         placement:
           constraints: [node.hostname == VM_16_8_centos]      
     mysql-slave:
       restart: always
       image: mysql:5.7.18
       environment:
         MYSQL_ROOT_PASSWORD: admin
       volumes:
         - /data/v-yinfu/mysql/slave/data:/var/lib/mysql
         - /data/v-yinfu/mysql/slave/config:/etc/mysql/conf.d
       ports:
         - target: 3306
           published: 14037
           protocol: tcp
           mode: host   
       deploy:
         replicas: 1
         restart_policy:
           condition: on-failure
         placement:
           constraints: [node.hostname == VM_16_13_centos]

  2. 授权

    进入主库容器执行mysql -uroot -p

    1
    2
    3
    4
    5
    #ip为从库ip，设置为只有从库可以访问
    GRANT REPLICATION SLAVE ON *.* TO 'app_sync'@'172.16.16.13' IDENTIFIED BY 'password';
    GRANT ALL ON *.* TO 'app_root'@'%' IDENTIFIED BY 'password';
    GRANT SELECT ON *.* TO 'app_read'@'%' IDENTIFIED BY 'password';
    FLUSH PRIVILEGES;

  3. 然后执行show master status;

    1
    2
    3
    4
    5
    6
    +------------------+----------+--------------+--------------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
    +------------------+----------+--------------+--------------------------+-------------------+
    | mysql-bin.000003 |     1164 | shenqics     | information_schema,mysql |                   |
    +------------------+----------+--------------+--------------------------+-------------------+
    1 row in set (0.00 sec)

  4. 进入从库容器执行mysql -uroot -p

    1
    2
    3
    4
    5
    6
    7
    stop slave;
    change master to master_host='172.16.16.8',master_port=14036,master_user='app_sync',master_password='password',master_log_file='mysql-bin.000003', master_log_pos=1164;
    start slave;
    show slave status;
    #授权
    GRANT SELECT ON *.* TO 'app_read'@'%' IDENTIFIED BY 'password';
    FLUSH PRIVILEGES;

  5. 执行show slave status\G;检查是否这两个为yes

    1
    2
    Slave_IO_Running: Connecting
    Slave_SQL_Running: Yes

  6. 重新注册

    1
    2
    3
    4
    5
    6
    stop slave;
    change master to master_host='172.16.16.8',master_port=14036,master_user='app_sync',master_password='admin',master_log_file='mysql-bin.000005', master_log_pos=361;
    start slave;
    #清除log,执行start slave报错时
    reset slave;
    start slave;

  7. 再次执行第6步检查

mysql创建用户命令详解

创建用户

GRANT 权限 ON 数据库.表名 TO '用户'@'主机' IDENTIFIED BY '密码'

  • 权限：all,select,等
  • 主机：指定ip地址访问、localhost或127.0.0.1（本地访问）、%（任意主机均可访问）
  • 密码：为空时则不需要密码

eg: 

1
2
GRANT REPLICATION SLAVE ON *.* TO 'app_sync'@'172.16.16.13' IDENTIFIED BY 'admin';
#意思是创建一个专门的用户（app_sync）进行从库复制，复制任何库和任何表,密码是admin,可以访问的ip只有来源172.16.16.13（从库ip）

删除用户drop user test@'172.16.16.13';

HikariCP连接池优化

HikariCP作为springboot连接池，在性能压测时，连接时间成指数递增。

1
2
3
4
5
6
7
8
9
10
11
#该属性用于控制连接在池中的最大生存时间，超过该时间强制逐出,当前正在使用的连接不会强制逐出。
#缺省：1800000， 即30min
spring.datasource.hikari.max-lefetime = 0
#配置允许连接池达到的最大连接数,cpu核心的两倍
spring.datasource.hikari.maximum-pool-size=50
spring.datasource.hikari.connection-test-query=select 1
spring.datasource.hikari.idle-timeout=60000
#建议比数据库的connect_timeout大一些
spring.datasource.hikari.connection-timeout=2000
spring.datasource.hikari.validation-timeout=1500
spring.datasource.hikari.minimum-idel=5

数据库配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
> show variables like '%timeout%';
connect_timeout	60
delayed_insert_timeout	300
have_statement_timeout	YES
innodb_flush_log_at_timeout	1
innodb_lock_wait_timeout	50
innodb_rollback_on_timeout	OFF
interactive_timeout	28800
lock_wait_timeout	31536000
mysqlx_connect_timeout	30
mysqlx_idle_worker_thread_timeout	60
mysqlx_port_open_timeout	0
net_read_timeout	30
net_write_timeout	60
rpl_stop_slave_timeout	31536000
slave_net_timeout	10
wait_timeout	28800

常见错误

  1. Too many connections症状，不断重启运行springboot并访问，出现如下错误

    1
    2
    3
    4
    5
    6
    2018-04-05 21:48:56.824 ERROR 6838 --- [           main] com.xhzg.xhzg.XhzgApplicationTests       : nested exception is org.apache.ibatis.exceptions.PersistenceException: 
    ### Error querying database.  Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"
    ### The error may exist in com/xhzg/xhzg/mapper/UserMapper.java (best guess)
    ### The error may involve com.xhzg.xhzg.mapper.UserMapper.loadUserByUsername
    ### The error occurred while executing a query
    ### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"

    解决：快速解决重启mysql释放Threads_connected连接数，或者等待一会儿，也会慢慢释放连接数,另一种更改max_connections最大连接数启动mysql添加参数--ulimit nofile=65536:65536

    参考：

    Docker容器中MySQL最大连接数被限制为214的解决方案

    [Increasing mysql max_connections to 1024 in a docker container]

  2. 错误

    1
    [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

    解决：

    删除配置文件sql_mode=ONLY_FULL_GROUP_BY这个属性值

    1
    2
    mysql>SELECT @@sql_mode;
    mysql>SELECT @@GLOBAL.sql_mode;

    参考：https://www.zhihu.com/question/37942423

  3. navicat客户端，连接mysql 8.0以上报错,提示授权啥的错误

  4. 设置主从模式时，使用用户'app_sync'@'172.16.16.13'连接时提示，以及一直Slave_IO_Running: Connecting

    1
    ERROR 1045 (28000): Access denied for user 'app_sync'@'10.255.0.2' (using password: YES)

    解决：部署时设置host模式

    原因：非host模式连接时,访问客户端ip是内部ip不是host的ip

  5. 错误

    1
    java.sql.SQLException: Incorrect string value: '\xF0\x9F\x90\xB6' for column 'UserNickname' at row 1

    解决：

    1. 在mysql配置文件添加后，重启

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      [client]
      default-character-set = utf8mb4

      [mysql]
      default-character-set = utf8mb4

      [mysqld]
      character-set-client-handshake = FALSE
      character-set-server = utf8mb4
      collation-server = utf8mb4_unicode_ci

    2. 修改数据表的编码

      ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8mb4;

    3. 修改数据库连接

      jdbc:mysql://localhost:3306/"+DATABASENAME+"?useunicode=true&characterEncoding=utf8

      方式一：去掉参数&characterEncoding=utf8和useunicode=true

      方式二(建议)：添加autoReconnect=true