MySQL

参考

Mysql Official Document

Comment Syntax

卸载 MySQL

卸载 yum 安装的 MySQL

$ yum remove -y mysql*

# 验证卸载情况,如果存在继续删
$ rpm -qa | grep -i mysql

# 删除其他相关文件
$ rm -rf /var/lib/mysql
$ rm /etc/my.cnf

卸载 rpm 安装的 MySQL

# 查询已经安装的 mysql 包
$ rpm -qa | grep -i mysql
MySQL-test-5.5.54-1.el6.x86_64
MySQL-shared-5.5.54-1.el6.x86_64
MySQL-server-5.5.54-1.el6.x86_64
MySQL-client-5.5.54-1.el6.x86_64
MySQL-devel-5.5.54-1.el6.x86_64
MySQL-embedded-5.5.54-1.el6.x86_64
MySQL-shared-compat-5.5.54-1.el6.x86_64

# 如果有依赖卸载不了可以加参数 --nodeps 强制卸载
$ rpm -e MySQL-test-5.5.54-1.el6.x86_64 MySQL-shared-5.5.54-1.el6.x86_64 MySQL-server-5.5.54-1.el6.x86_64 ...

# 删除服务
$ chkconfig --list | grep -i mysql
$ chkconfig --del mysql
# 或者
$ systemctl disable mysqld

# 删除相关文件
$ whereis mysql 或者 find / -name mysql
$ mysql: /usr/bin/mysql /usr/lib64/mysql /usr/include/mysql /usr/share/mysql
$ rm -rf /usr/lib/mysql
$ rm -rf /usr/lib64/mysql
$ rm -rf /usr/include/mysql
$ rm -rf /usr/share/mysql
$ rm -rf /usr/my.cnf

安装 MySQL

rpm 包安装

建议安装 rpm 包,省时省力,只需要指定root密码,不需要额外配置什么东西就可以启动了。

$ rpm -ivh mysql-community-client-5.7.22-1.el7.x86_64.rpm \
           mysql-community-common-5.7.22-1.el7.x86_64.rpm \
           mysql-community-libs-5.7.22-1.el7.x86_64.rpm \
           mysql-community-server-5.7.22-1.el7.x86_64.rpm

对于 Mysql5.7+ 的版本,为了加强安全性,为自动为 root 用户随机生成了一个密码,对于 RPM 安装的 Mysql,默认是/var/log/mysqld.log。并且只有在第一次启动 Mysql 才可以在日志中查看临时密码!

如果很不幸你忘记了密码,可以在/etc/my.cnf中添加skip-grant-tables,然后重启 Mysql,直接进入 Mysql 控制台,然后修改密码就可以了。

mysql> update mysql.user set authentication_string=password('123456') where user='root' and host='localhost';

然后注释掉/etc/my.cnf中的skip-grant-tables,重启就可以了。

如果修改密码的时候出现Your password does not satisfy the current policy requirements,说明你设置的密码不符合安全性规范,如果你就是想设置的简单一些,可以修改两个参数。

# validate_password_policy 的取值以及含义:
# 0: low      密码检查标准 Length
# 1: medium   密码检查标准 Length; numeric, lowercase/uppercase, and special characters
# 2: strong   密码检查标准 Length; numeric, lowercase/uppercase, and special characters; dictionary file
mysql> set global validate_password_policy=0;
# validate_password_length 参数默认为8,它有最小值的限制,最小值为4,即便你设置成1,它实际也会变成4
mysql> set global validate_password_length=4;

压缩包安装方式

$ wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz

$ tar -zxvf mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz -C /usr/local/

$ cd /usr/local/

$ ln -s /usr/local/mysql-5.6.33-linux-glibc2.5-x86_64 mysql

$ cd mysql-5.6.33-linux-glibc2.5-x86_64

# deprecated in 5.7 
$ scripts/mysql_install_db --user=despacito
# 5.7
$ bin/mysqld --initialize --user=despacito (super user with random passwd in log) 
# 或者
$ bin/mysqld --initialize-insecure --user=despacito (no passwd) 

# 将启动脚本 mysql.server 放到 /etc/init.d 目录下,这样就可以使用 service 命令了
$ cp support-files/mysql.server /etc/init.d/mysqld
$ chmod 755 /etc/init.d/mysqld

# mysql 配置文件
$ cp support-files/my-default.cnf /etc/my.cnf

# 修改启动脚本
$ vim /etc/init.d/mysqld
# mysql 安装目录
basedir=/usr/local/mysql/
# 数据存储目录
datadir=/usr/local/mysql/data/

开启binlog日志

$ vim my.inf
# 开启 binlog
log_bin=ON
# binlog 日志基本文件名
log_bin_basename=/var/lib/mysql/mysql-bin
# binlog 的索引文件,用于管理所有 binlog 文件的目录
log_bin_index=/var/lib/mysql/mysql-bin.index

# 查看binlog是否开启成功
mysql> show variables like '%log_bin%'
+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| log_bin                         | ON                              |
| log_bin_basename                | /var/lib/mysql/mysql-bin        |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index  |
| log_bin_trust_function_creators | OFF                             |
| log_bin_use_v1_row_events       | OFF                             |
| sql_log_bin                     | ON                              |
+---------------------------------+---------------------------------+

成功开启binlog后,查看binlog文件索引信息

$ cat /var/lib/mysql/mysql-bin.index
/var/lib/mysql/mysql-bin.000001
/var/lib/mysql/mysql-bin.000002
/var/lib/mysql/mysql-bin.000003
/var/lib/mysql/mysql-bin.000004

启动/关闭 mysql 服务

# 查看命令
$ service mysql | service mysqld
# 支持的操作 {start|stop|restart|reload|force-reload|status}
# 例如启动服务,实际上执行的是 ./support-files/mysql.server start
$ service mysql start

# 或者使用 systemctl <option> mysqld

创建用户与分配权限

# 查看 mysql 详细用法
$ bin/mysql --help 

# root用户登录 
# 使用 -h 指定远程 mysql 服务器的 hostname,默认 localhost
$ mysql -u root -p

# 第一次登录必须修改生成的初始密码
# === 方式1 ===
$ bin/mysqladmin -u root -p password 新密码
# === 方式2 ===
mysql> SET PASSWORD = PASSWORD('your new password');
mysql> ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
mysql> flush privileges;

# 创建一个新数据库实例,在 mysql shell 中`;`是必须的
mysql> create database testDB;

# 创建一个新用户 test,并将 testDB 的所有权限赋给它。localhost 指只有本机可以登录访问
mysql> grant all privileges on testDB.* to test@localhost identified by '1234';
# "%" 表示除了本机之外的所有主机可以登录访问,要想本机和所有其他主机都可以就同时执行这两条命令
mysql> grant all privileges on testDB.* to test@"%" identified by '1234';

# 创建用户也可以直接操作系统用户表 mysql.user
mysql> insert into mysql.user(Host, User, Password) values("localhost", "test", password("1234"));

# 赋予某些权限,和所有数据库
mysql> select, insert, update, delete, create, drop on *.* to test@localhost identified by '1234';

# Note:8.0 语法
mysql> CREATE USER 'root'@'%' IDENTIFIED BY '你的密码';
mysql> GRANT ALL ON *.* TO 'root'@'%';
# 修改密码
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你的密码';

# 记得刷新系统权限表
mysql> flush privileges; 

其他命令

# 删除用户
mysql> Delete FROM mysql.user Where User='test' and Host='localhost';

# 修改用户密码
mysql> update mysql.user set password=password('新密码') where User="test" and Host="localhost";
mysql> flush privileges;

# 列出所有数据库
mysql> show databases;

# 切换数据库
mysql> use '数据库名';

# 列出所有表
mysql> show tables;

# 显示表结构
mysql> describe 表名;

# 删除数据库和表
mysql> drop database 数据库名;
mysql> drop table 数据表名;

忘记 mysql 用户名密码

# 首先关闭MySQL实例
$ bin/mysqld stop | service mysqld stop

# 安全模式启动MySQL
$ bin/mysqld_safe --skip-grant-tables &

# 进入MySQL Command Line
$ bin/mysql

# 进来之后,就可以修改密码了
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');
mysql> flush privileges;

数据导出/导入

数据导出

# 查看基本使用方法
$ mysqldump

# 查看详细的使用方法
$ mysqldump --help 

# 导出表
$ mysqldump -uroot -p dbname table1 table2 > /root/data.sql

# 导出指定数据库
$ mysqldump -uroot -p --databases dbname1 dbname2 dbname3 > /root/data.sql

# 导出所有数据库
$ mysqldump -uroot -p --all-databases > /root/all_databases_data.sql

数据导入

$ mysql -uroot -p

mysql> source /root/all_databases_data.sql

如果有多个 sql 文件需要导入数据库,可以新建一个文件all.sql

$ vim all.sql
# 添加下面的内容
source /root/data1.sql
source /root/data2.sql

# 然后去 mysql 控制台执行
mysql> source /root/all.sql

导出表结构到 Excel

SELECT 
    COLUMN_NAME 列名,
    COLUMN_TYPE 列类型,
    DATA_TYPE 数据类型,
    CHARACTER_MAXIMUM_LENGTH 数据长度,
    IS_NULLABLE 是否为空,
    COLUMN_KEY 键类型,
    COLUMN_DEFAULT 默认值,
    COLUMN_COMMENT 备注
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA = 'your_db_name'
AND
    TABLE_NAME = 'your_table_name'

异常记录

corruption in the InnoDB tablespace

这种错误一般都是由于断电(power breakdown)导致的。

$ less /var/log/mysqld.log

2019-05-22 21:59:55 0x7fad69ffb700  InnoDB: Assertion failure in thread 140382784435968 in file fut0lst.ic line 85
InnoDB: Failing assertion: addr.page == FIL_NULL || addr.boffset >= FIL_PAGE_DATA
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
13:59:55 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=0
max_threads=1000
thread_count=0
connection_count=0
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 405567 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7fad500008c0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7fad69ffadb0 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xf0702b]
/usr/sbin/mysqld(handle_fatal_signal+0x461)[0x7b93a1]
/lib64/libpthread.so.0(+0xf5d0)[0x7fad8b55b5d0]
/lib64/libc.so.6(gsignal+0x37)[0x7fad89f44207]
/lib64/libc.so.6(abort+0x148)[0x7fad89f458f8]
/usr/sbin/mysqld[0x789704]
/usr/sbin/mysqld[0x78922c]
/usr/sbin/mysqld[0x10a0815]
/usr/sbin/mysqld[0x10a2918]
/usr/sbin/mysqld(_Z9trx_purgemmb+0x3c9)[0x10a5119]
/usr/sbin/mysqld(srv_purge_coordinator_thread+0xded)[0x107d72d]
/lib64/libpthread.so.0(+0x7dd5)[0x7fad8b553dd5]
/lib64/libc.so.6(clone+0x6d)[0x7fad8a00bead]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): Connection ID (thread ID): 0
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

看错误日志是InnoDB tablespace文件损坏,直接导致mysql崩溃后启动不了了。看下日志中的线索https://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html,可以在/etc/my.cnf中配置参数innodb_force_recovery为大于0的值以强制启动 InnoDB Storage Engine 以便将实际的数据文件 dump 出来,我这里改成1也没有成功启动,文档中介绍必要情况(比如还是起不来)可以调大该参数的值,最大是6(只读模式)。

# 备份原始数据库文件,不知道原始数据存在哪可以查看 /etc/my.cnf 的配置项 datadir=/data/mysql
$ mv /data/mysql /data/mysql_bak20190522

# 修改参数以强制启动 mysql innodb
$ vim /etc/my.cnf
[mysqld]
innodb_force_recovery = 6
$ systemctl start mysqld

# 导出所有数据
$ mysqldump -uroot -p --all-databases > /data/all_databases_data.sql

# 重装 mysql
$ rpm -qa | grep mysql
mysql-community-common-5.7.24-1.el7.x86_64
mysql-community-libs-compat-5.7.24-1.el7.x86_64
mysql-community-libs-5.7.24-1.el7.x86_64
mysql-community-server-5.7.24-1.el7.x86_64
mysql-community-client-5.7.24-1.el7.x86_64
$ rpm -e --nodeps mysql-community-common-5.7.24-1.el7.x86_64 ...
$ rpm -ivh mysql-community-common-5.7.24-1.el7.x86_64 ...

# 查看最近生成的临时密码
$ cat /var/log/mysqld.log | grep "temporary password" 
H+:*lcgPw3SD

$ mysql -uroot -pH+:*lcgPw3SD
# 修改初始密码
mysql> set password = password('your new password');
# 设置密码永不过期
mysql> alter user 'root'@'localhost' password expire never;
# 开启远程节点访问权限
mysql> grant all privileges on *.* to [email protected]"%" identified by 'your new password';
# 刷入操作
mysql> flush privileges;
# 导入数据
mysql> source /data/all_databases_data.sql

# 重启 mysql
# 理论上不需要重装 mysql 只要找到损坏的文件/表删掉就可以了,当然这也取决于你坏的到底是什么文件,我们这里坏的比较彻底,并且
# 里面存的是各个集群的元数据信息,数据量也不是很大,因此这个方式是最无脑也是最快的恢复的方式了。

mysql 默认innodb_force_recovery = 0,normal startup without forced recovery,当指定为较大的值时会包含所有更小值的功能,比如3包含了1和2的所有功能。如果能够使用innodb_force_recovery = 3或更低的值来转储表,只有损坏的单个页面上的某些数据会丢失,相对安全。4或更高就比较危险了,因为数据文件可能会永久损坏。6被认为是激烈的,因为数据库页面处于过时状态,这反过来可能会在B树和其他数据库结构中引入更多损坏。作为一种安全措施,当innodb_force_recovery大于0时,InnoDB 会阻止INSERTUPDATEDELETE操作。如果innodb_force_recovery设置为4或更高,InnoDB 会以只读模式启动。

打赏一个呗

取消

感谢您的支持,我会继续努力的!

扫码支持
扫码支持
扫码打赏,一毛也是爱

打开支付宝扫一扫,即可进行扫码打赏哦