总结一些MySQL的常用命令,已备以后忘记了。。。
免密码登录
[root@xiexianbin_cn ~]# cat .my.cnf
[client]
user=root
password=pass
创建用户
GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'root';
GRANT ALL ON *.* TO 'root'@'localhost' IDENTIFIED BY 'root' WITH GRANT OPTION;
FLUSH PRIVILEGES;
GRANT ALL ON test.* TO 'root'@'%' IDENTIFIED BY 'test';
GRANT ALL ON test.* TO 'root'@'localhost' IDENTIFIED BY 'test' WITH GRANT OPTION;
FLUSH PRIVILEGES;
查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| data1 |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.01 sec)
创建数据库
CREATE DATABASE IF NOT EXISTS data1 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
mysql> create database date2;
Query OK, 1 row affected (0.01 sec)
mysql -uroot -proot -Dmysql -e"select host,user from user";
for name in a b c; do
mysql -uroot -proot -Dmysql -e"CREATE DATABASE IF NOT EXISTS ${name}_t DEFAULT CHARSET utf8 COLLATE utf8_general_ci;";
mysql -uroot -proot -Dmysql -e"CREATE DATABASE IF NOT EXISTS ${name}_o DEFAULT CHARSET utf8 COLLATE utf8_general_ci;";
done
删除数据库
drop database data1;
创建表
CREATE TABLE training(
id INT(11) PRIMARY KEY,
col1 VARCHAR(20) NOT NULL,
START DATE);
查看表创建命令
mysql> show create table t_config \G
*************************** 1. row ***************************
Table: t_config
Create Table: CREATE TABLE `t_config` (
`id` int(11) NOT NULL,
`col2` varchar(36) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
查看log类型
show variables like 'log_%';
查看binlog类型
show variables like 'bin%';
show binary logs;
MySQL跳过1062错误
在my.cnf设置的[mysqld]下配置
slave-skip-errors=1062
查看mysql版本
show variables like '%version%';
查看binlog的相关参数
show variables like '%binlog%';
查看mysql服务器数据文件
show variables like '%datadir%';
查看系统变量控制二进制日志自动删除的天数
show variables like 'expire_log%';
set global expire_logs_days=7;
select @@expire_logs_days;
查看MySQL支持的存储引擎
show engines;
查看innodb的状态
show engine innodb status;