MySQL 启动关闭方法

service方式启动关闭

正常安装的MySQL实例会将安装的mysql.service复制至/etc/init.d/目录下，并配置chkconfig，该方式安装的MySQL可以使用service管理工具进行启动和关闭MySQL即可

1
2
3
4
5
# 查看是否有注册service服务
chkconfig --list | grep mysql

# 启动和关闭mysql服务
service mysqld stop|start|restart|status

未注册MySQL服务

未注册MySQL服务，需要对MySQL的目录结构有一定了解，通过mysqld_safe去开启MySQL服务，通过mysqladmin去关闭该服务

注：首次拿到MySQL服务器，对MySQL的目录结构不了解的情况下，建议使用ps -ef | grep mysql命令查看已有的MySQL进程信息，将该信息保留下来方便后续对该服务进行操作

1
2
3
4
[root@mysql2 opt]# ps -ef | grep mysql
root   3691   1 0 12:33 ?    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/mysql2.pid

mysql   4738 3691 34 13:43 ?    00:03:20 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/var/lib/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/lib/mysql/mysqld.log --open-files-limit=65535 --pid-file=/var/lib/mysql/mysql2.pid --socket=/var/lib/mysql/mysql.sock --port=3306

从上面的例子可以看出，该MySQL的软件所在目录/usr/local/mysql/bin，mysql的配置文件使用的是默认的/etc/my.cnf，socket文件路径在/var/lib/mysql/mysql.sock等

通过上面的进程信息，编写如下命令就可以关闭mysql服务，非紧急状况下不建议直接kill MySQL的进程。关闭该MySQL实例的方法：

1
/usr/local/mysql/bin/mysqladmin -uroot -p --socket=/var/lib/mysql/mysql.sock shutdown

启动上面关闭掉的MySQL实例

1
/usr/local/mysql/bin/mysqld_safe --default-file=/etc/my.cnf --user=mysql &

MySQL 主从启动关闭顺序

  • 启动MySQL主从

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    1.启动主库实例
    service mysqld start

    2.启动从库实例
    service mysqld start

    3.启动从库日志应用
    SQL > start slave;

    4.查看从库应用状态
    SQL > show slave status \G

  • 关闭MySQL主从环境

    1
    2
    3
    4
    5
    6
    7
    8
    1.关闭从库日志应用
    SQL > stop slave;

    2.关闭从库实例
    service mysqld stop

    3.关闭主库实例
    service mysqld start

  • 注意：MySQL默认启动是不会执行start slave操作，所以每次启动关闭主备环境的实例时要在从库上执行start slave命令去手工启动日志应用服务

MySQL配置必要参数说明

初始化前参数检测：

1
2
3
4
5
6
7
8
9
10
11
SHOW VARIABLES WHERE variable_name in (
 'log_bin_trust_function_creators',
 'transaction_isolation',
 'lower_case_table_names',
 'sql_mode',
 'character_set_server',
 'default_character_set',
 'innodb_large_prefix',
 'max_connections',
 'innodb_buffer_pool_size'
)

说明：
lower_case_table_names = 1 忽略表明大小写
log_bin_trust_function_creators=1 MySQL带有函数的SQL操作模式
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES MySQL的严格模式
transaction_isolation = READ-COMMITTED 使用RC隔离级别
innodb_large_prefix=ON 允许innodb使用长索引类型，单列索引长度上限扩展到3072个字节，不开启将导致varchar较长的字段类型索引创建失败

SYS库下面的常用性能视图说明

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
x$innodb_lock_waits								查看当前innodb的锁状态信息

x$statements_with_full_table_scans				统计数据库中哪些查询使用了全盘扫描

x$statements_with_errors_or_warnings			error或者waring状态的SQL统计

processlist										当前数据库

schema_table_lock_waits							查询当前被锁住的表

io_global_by_file_by_bytes						统计每个表文件的IO信息

innodb_buffer_stats_by_table					统计每个表占用buffer的信息

schema_table_statistics							统计表的状态信息，包括被更新，插入多少次等

x$statement_analysis							查看SQL的统计信息

x$statements_with_runtimes_in_95th_percentile	SQL执行时间较久的统计

x$statements_with_sorting						排序操作较多的SQL统计

x$statements_with_temp_tables					使用临时表空间较多的SQL统计

举例：

产看写入操作较多的表的信息

1
select `file`,`count_read`,`total_read`,`avg_read`,`count_write`,`total_written`,`avg_write`,`total`,`write_pct` from sys.io_global_by_file_by_bytes where file like '%ibd' order by count_write desc limit 10;

MySQL估算数据库大小

1
SELECT CONCAT(ROUND(SUM(data_length/1024/1024),2),'MB') AS data_length_MB,CONCAT(ROUND(SUM(index_length/1024/1024),2),'MB') AS index_length_MB FROM information_schema.TABLES WHERE table_schema='sys'

命令端估算只统计了表和索引文件大小，完整 MySQL占用空间需datadir目录下的所有文件

MySQL 解锁用户

当用户短时间内在同一个IP持续有中断到到MySQL请求，超过max_connect_errors的限制时，再进行连接是MySQL会抛出ERROR 1129 (00000)的异常信息

1
2
3
4
5
6
# 此时需执行FLUSH HOSTS命令进行解锁。
SQL > FLUSH HOSTS;

# 或者可以使用mysqladmin进行刷新

[root@localhost ~]# mysqladmin  -u  root  -p flush-hosts

MySQL 死锁处理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 查询是否锁表
SHOW OPEN TABLES WHERE In_use > 0;

# 查看正在锁的事务
select * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
select * from performance_schema.data_locks; # 8.0版本

# 查看等待锁的事务
select * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
select * from performance_schema.data_lock_waits; # 8.0版本

# 查看进程信息
SHOW PROCESSLIST; # 或者
select * FROM INFORMATION_SCHEMA.PROCESSLIST; # 或者
select * FROM SYS.PROCESSLIST;

# KILL被锁住的表进程
KILL ID; (ID为 SHOW PROCESSLIST获得的ID标识号)

MySQL 用户管理

  • 创建用户ecology

    1
    CREATE USER 'dev'@'192.168.1.%' IDENTIFIED BY "dev123";

  • 赋予dev用户允许190.168.1.0/24网段登入权限，允许该用户增删改查dev数据库下面的对象

    1
    grant select,update,delete,insert on dev.* to dev@'192.168.1.%';

  • MySQL 5.7版本可以使用一条命令实现上面的用户创建

    1
    GRANT select,update,delete,insert ON dev.* TO dev@'192.168.1.%' IDENTIFIED BY "dev123";

  • 赋予all privileges权限，下面给出创建用户模板

    1
    GRANT ALL PRIVILEGES ON *.* TO 'dev'@'192.168.1.%' IDENTIFIED BY "dev123";

  • 查看用户赋权信息：

    1
    2
    show grants for 'dev'@'192.168.1.%';
    select * from mysql.user;

  • 删除用户

    1
    drop user 'dev'@'192.168.1.%';

  • 刷新权限：每次权限有新更改或者创建删除用户后记得刷新权限，使的新的权限生效

    1
    FLUSH PRIVILEGES;

MySQL 重要参数优化

  • max_connections = 3000

    • 运行MySQL的最大连接数，根据客户需求进行配置

  • innodb_buffer_pool_size = 8G

    • InnoDB的缓冲池大小，使用缓存池取数据，可以降低数据库I/O次数，设置过大会导致数据库的换页操作频繁甚至达到瓶颈，实例crash等，推荐使用内存的50%-80%即可

  • max_allowed_packet = 32M

    • 服务所能处理的请求包的最大大小以及服务所能处理的最大的请求大小，长SQL相对设置的可以较大，一般设置32M足够使用

  • innodb_file_io_threads = 8

    • innodb用来同步IO操作的IO线程的数量，Linux下推荐设置为8

  • innodb_thread_concurrency = 16

    • InnoDb 核心内的允许线程数量，推荐设置为CPU的个数或者CPU个数的倍数

  • innodb_flush_log_at_trx_commit = 0

    • 控制MySQL刷新事务日志到磁盘的方式，从0-2，对数据的保护等级越来越高，对数据库的性能影响也越来越大，建议使用0

  • innodb_log_buffer_size = 16M

    • 用来缓冲日志数据的缓冲区的大小，一般16M或者32M即可

MySQL root密码丢失处理

  • 没有记住MySQL数据库root用户默认密码（为初始化安装mysql时默认生成）

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    # 修改/etc/my.cnf文件，在[mysqld]的段中加上一句：skip-grant-tables
    # 重启mysql服务
    [root@localhost ~]# service mysqld restart
    # 以无密码方式进入mysql：
    [root@localhost ~]# /usr/local/mysql/bin/mysql -uroot -p
    Enter password: 直接回车
    mysql>use mysql
    输出：Database changed
    mysql> update user set authentication_string='' where user='root';
    # 重置密码后再将/etc/my.cnf文件中添加的“skip-grant-tables”注释掉
    # 重启mysql服务
    [root@localhost ~]# service mysqld restart
    [root@localhost ~]# /usr/local/mysql/bin/mysql -uroot -p
    Enter password:直接回车
    mysql> set password = '123.com';
    输出：Query OK, 0 rows affected (0.02 sec)
    mysql> flush privileges;
    输出：Query OK, 0 rows affected (0.00 sec)

  • 重新进入mysql，验证root密码是否修改成功

MySQL 重新初始化实例

  • 关闭mysql服务

    1
    service mysqld stop

  • 清理datadir(本例中指定的是/var/lib/mysql)指定的目录下的文件，将该目录下的所有文件删除或移动至其他位置

    1
    2
    cd /var/lib/mysql
    mv * /opt/mysql_back/

  • 初始化实例

    1
    /usr/local/mysql/bin/mysqld --initialize --user=mysql

  • 重复安装过程中创建用户的步骤，修改默认密码，重新创建数据库和用户即可

    1
    2
    3
    4
    5
    6
    /usr/local/mysql/bin/mysql -uroot -p
    mysql> set password = 'mysqltest123';
    mysql>create database test;
    mysql> create user dev identified with mysql_native_password by 'dev123';
    mysql> grant all privileges on *.* to 'dev'@'%';
    mysql> flush privileges;

连接数据库报错

  • 问题描述：日志报错Public Key Retrieval is not allowed的解决方法

  • 解决方法：

    1
    2
    3
    # 1.在jdbc url上添加&allowPublicKeyRetrieval=true
    # 2.修改数据库的密码加密方式：
    alter user xxx@'%' identified with mysql_native_password by "xxx";