Elvea Huang

Appearance

MySQL ​

下载地址 ​

bash
# MySQL Community Server
https://cdn.mysql.com//Downloads/MySQL-8.4/mysql-8.4.0-macos14-x86_64.dmg
https://cdn.mysql.com//Downloads/MySQL-8.4/mysql-8.4.0-macos14-arm64.dmg
https://cdn.mysql.com//Downloads/MySQL-8.4/mysql-8.4.0-winx64.zip
https://cdn.mysql.com//Downloads/MySQL-8.4/mysql-8.4.0-1.el7.x86_64.rpm-bundle.tar
https://cdn.mysql.com//Downloads/MySQL-8.4/mysql-8.4.0-1.el8.x86_64.rpm-bundle.tar
https://cdn.mysql.com//Downloads/MySQL-8.4/mysql-8.4.0-1.el9.x86_64.rpm-bundle.tar
# MySQL WorkBench
https://cdn.mysql.com//Downloads/MySQLGUITools/mysql-workbench-community-8.0.36-macos-x86_64.dmg
https://cdn.mysql.com//Downloads/MySQLGUITools/mysql-workbench-community-8.0.36-macos-arm64.dmg
https://cdn.mysql.com//Downloads/MySQLGUITools/mysql-workbench-community-8.0.36-winx64.msi

死锁 ​

查看死锁和去除死锁

sql
#
select *
from information_schema.innodb_trx;
select *
from performance_schema.data_locks;
select *
from performance_schema.data_lock_waits;
#
select *
from information_schema.innodb_trx;
select *
from information_schema.innodb_locks;
select *
from information_schema.innodb_lock_waits;

创建用户 ​

sql
# 创建用户-外网
CREATE USER 'root'@'%' IDENTIFIED BY 'Root@007';
# 创建用户-内网
CREATE USER 'root'@'192.168.%' IDENTIFIED BY 'Root@007';

修改密码 ​

sql
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Root@007';
ALTER USER 'root'@'192.168.%' IDENTIFIED BY 'Root@007';

授权 ​

sql
# 授权
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
# 刷新权限
flush privileges;

删除用户 ​

sql
# 删除内网账号
DROP USER 'root'@'192.168.%';
# 删除外网账号
DROP USER 'root'@'%';

数据库备份 ​

bash
# 备份所有数据库
mysqldump -u username -p --all-databases > db.sql
# 备份单个数据库
mysqldump -u username -p dbname > db.sql
# 备份单表
mysqldump -u username -p dbname tablename > table.sql

数据库还原 ​

bash
# 还原单个数据库
mysql -u root -p dbname < db.sql
# 后台还原数据库
nohup `/usr/bin/mysql -uroot -proot platform_pro < /data/app/temp/db.sql` > ./db.log 2>&1 &
bash
# 还原单个数据库
use db
source db.sql

还原数据库速度慢

bash
# 1. 查看变量innodb_flush_log_at_trx_commit
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
# 2. 查询结果如果是1的话，将其修改成0
SET GLOBAL innodb_flush_log_at_trx_commit = 0;
# 3.这时候再source，会发现速度快了很多，在结束后记得把这个变量重新设置成1
SET GLOBAL innodb_flush_log_at_trx_commit = 1;

手工清理日志 ​

查看所有日志文件

sql
show binary logs;

结果如下

+-----------------------------+-----------+-----------+
| Log_name                    | File_size | Encrypted |
+-----------------------------+-----------+-----------+
| mysql-master-log-bin.000002 |       179 | No        |
| mysql-master-log-bin.000003 |       156 | No        |
+-----------------------------+-----------+-----------+
2 rows in set (0.00 sec)

查看当前日志文件

sql
show master status;

结果如下

+-----------------------------+----------+--------------+-------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-master-log-bin.000003 | 156 | lms,test | information_schema,performance_schema,mysql,sys | |
+-----------------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)

删除日志文件

删除日志应该排除当前日志文件，一定不能错。

sql
purge binary logs to 'mysql-master-log-bin.000003';