MySQL
MySQL Documentation: https://dev.mysql.com/doc/
常用命令
连接数据库
mysql -uroot -p
-- 创建用户
CREATE USER 'test'@'%' identified BY 'test@123!';
-- CREATE USER 'test'@'%' identified WITH 'mysql_native_password' BY 'test@123!';
GRANT ALL PRIVILEGES ON tests.* TO 'test'@'%';
FLUSH PRIVILEGES;
-- 删除用户
DROP USER 'test'@'%';
FLUSH PRIVILEGES;
-- 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'
SHOW PROCESSLIST;
-- 查看MySQL状态
STATUS;
-- 显示支持的引擎
SHOW ENGINES;
-- 显示所有数据库
SHOW DATABASES;
-- 创建数据库
CREATE DATABASE testdb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SHOW CREATE DATABASE testdb;
-- 切换数据库上下文,即设置当前会话的默认数据库
USE testdb;
-- 显示本数据库所有的表
SHOW TABLES;
-- 创建表
CREATE TABLE users
(
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
`code` varchar(255) NOT NULL DEFAULT '' COMMENT 'Code',
`name` varchar(255) NOT NULL DEFAULT '' COMMENT 'Name',
`channel` json DEFAULT NULL COMMENT 'Channel',
`count` int NOT NULL DEFAULT '0' COMMENT 'Count',
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `users_code_index` (`code`),
KEY `users_code_status_index` (`code`, `count`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4;
SHOW CREATE TABLE users;
-- 插入数据
INSERT INTO `users` (`name`) VALUES ('admin');
INSERT INTO `users` VALUES (2, 'A01', 'admin2', '["A01", "A02"]', '1', NOW(), NOW(), null);
INSERT INTO `users` (`code`, `name`, `channel`) VALUES ('A01', 'admin3', '["A01", "A03"]'), ('A01', 'admin4', '["A04", "A05"]');
-- 查询数据
SELECT * FROM `users`;
SELECT * FROM `users` WHERE json_overlaps(`channel`, convert('["A02","A03"]', JSON));
-- 复制数据
drop table users;
create table users select * from users_backup;
-- 建立索引
ALTER TABLE 表名 ADD INDEX 索引名 列名;
ALTER TABLE 表名 ADD UNIQUE 索引名 列名;
ALTER TABLE 表名 ADD PRIMARY KEY 索引名 列名;
CREATE INDEX 索引名 ON 表名 列名;
CREATE UNIQUE INDEX 索引名 ON 表名 列名;
-- 删除索引
DROP index 索引名 ON 表名 列名;
ALTER TABLE 表名 DROP INDEX 索引名 列名;
ALTER TABLE 表名 DROP UNIQUE 索引名 列名;
ALTER TABLE 表名 DROP PRIMARY KEY 索引名 列名;
NOW
mysql> SELECT NOW(), UNIX_TIMESTAMP(), UNIX_TIMESTAMP('2015-10-31 12:00:00'), DATE('2015-10-31 12:00:00');
+---------------------+------------------+---------------------------------------+-----------------------------+
| NOW() | UNIX_TIMESTAMP() | UNIX_TIMESTAMP('2015-10-31 12:00:00') | DATE('2015-10-31 12:00:00') |
+---------------------+------------------+---------------------------------------+-----------------------------+
| 2020-12-17 11:24:54 | 1608175494 | 1446264000 | 2015-10-31 |
+---------------------+------------------+---------------------------------------+-----------------------------+
LEFT
select count(*) from wx_ucenter_user where LEFT(openid,6) = 'ocF00a' and name is not null;
Explain
https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
复制
show master status;
start slave
show slave status\G;
change master to master_host='192.168.1.2', master_user='reader', master_password='reader', master_log_file='binglog.000002', master_log_pos=2377
show slave status\G;
show status
查看数据库当前支持的引擎
mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
慢查询日志
mysql> SHOW VARIABLES LIKE '%query%';
+------------------------------+--------------------------------+
| Variable_name | Value |
+------------------------------+--------------------------------+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | NO |
| long_query_time | 10.000000 |
| query_alloc_block_size | 8192 |
| query_prealloc_size | 8192 |
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/test01-slow.log |
+------------------------------+--------------------------------+
8 rows in set (0.00 sec)
mysql> SET GLOBAL slow_query_log=ON;
Query OK, 0 rows affected (0.03 sec)
mysql> SET long_query_time=2;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE '%query%';
+------------------------------+--------------------------------+
| Variable_name | Value |
+------------------------------+--------------------------------+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | NO |
| long_query_time | 2.000000 |
| query_alloc_block_size | 8192 |
| query_prealloc_size | 8192 |
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/test01-slow.log |
+------------------------------+--------------------------------+
8 rows in set (0.00 sec)
mysqldump
mysqldump -h 192.168.1.11 -uroot -p --databases shop > ./shop.sql
mysqldump -h 192.168.1.11 -uroot -p --single-transaction --databases shop --ignore-table=shop.users --ignore-table=shop.users_info > ./shop.sql
restore
mysql -u root -p testdb < testdb.sql
常见问题
mysql主从延迟：主从MYSQL，插入后立即读取可能会读不到，同步需要时间。