总结一些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;