MySQL使用随笔

001 查看版本

mysql --version
mysql > select version();
mysql > status;

002 新建MySQL用户、授权

insert into mysql.user(Host,User,Password) values("localhost","username",password("yourpassword"));

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY "yourpassword" WITH GRANT OPTION; 

flush privileges;

for 5.7：

flush privileges;

CREATE USER 'username'@'127.0.0.1' IDENTIFIED BY 'password';

grant all on banma.* to username@'127.0.0.1' identified by 'password';

 

003. 查询支持的存储引擎
show engines;

004 查看变量
show variables like 'innodb%';

005 导入数据库(sql文件)
mysql -uroot -p dbname < dbname.sql

006 my.cnf说明
[client] #客户端配置
port                              = 3306
socket                         = /var/lib/mysql/mysql.sock #本地客户端使用这个socket链接mysqld
default_character_set   = utf8
secure_auth                    = 0                         #跳过密码格式不统一问题                 

[mysqld]
datadir=/usr/local/mysql/data        #指定数据路径
socket=/var/lib/mysql/mysql.sock #使用这个socket启动server
user=mysql

skip-grant-tables           # 这个可以保证启动后不需要密码验证，适合修改密码

old_passwords = 1
secure_auth = 0
innodb_force_recovery=0          #必须为0，否则innodb就是read only模式
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

007 修改root密码
mysqld_safe --skip-grant-tables
mysql > update mysql.user set password=password("new_pass") where user="root";

008 让mysql服务器输出所有执行的SQL语句记录

mysqld --general-log=TRUE

日志输出为mysql服务器数据目录的localhost.log, 其他日志请执行mysqld --verbose --help查看

009 数据库导出导入

导出数据
mysqldump -uusername -ppassword databasename > databasename.sql
导出存储过程和函数
mysqldump -uusername -ppassword -ntd -R databasename > stored-procs.sql
只导出表结构
mysqldump -uusername -ppassword -d --add-drop-table databasename > table-init.sql
导入sql文件（可以为数据，表结构，存储过程...）
mysql -uusername -ppassword databasename < sql-file.sql

010 查看当前哪些线程在运行

mysql >show processlist;

在Info字段还可以查看执行的语句，如果有执行很慢的语句，可以直接查询到。

 

011 删除表记录

DELETE FROM tablename

TRUNCATE TABLE tablename

012 mysql存储过程的sql secuirty
mysql存储过程中sql secuirty决定了执行相关存储过程时的安全限制，sql secuirty可以为DEFINER或者INVOKER。
DEFINER表示执行此存储过程的用户必须是存储过程创建者且必须存在这个用户并有对应权限。
INVOKER表示执行此存储过程的用户必须是存在的用户且这个用户并有对应权限。

创建存储过程时，可以指定sql secuirty，以下例子指定sql secuirty为INVOKER。
CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()
SQL SECURITY INVOKER
BEGIN
  SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
END;

也可以通过修改mysql.proc表中对应字段security_type来设定sql secuirty。例
update mysql.proc set security_type = ...

或

alter procedure pro_name sql security invoker;

013 数据表增加字段（时间字段）

mysql> alter table mytable add column ctime datetime DEFAULT NOW() NOT NULL after flag;  
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

014 左、右join

语法：

SELECT * FROM tableA
LEFT|RIGHT JOIN tableB
ON tableA.field1 = tableB.field2
左JOIN：以左（tableA）表为基础查出所有符合条件的tableA和tableB，tableB不存在的记录显示tableB相关字段为NULL；

右JOIN：以右（tableB）表为基础查出所有符合条件的tableA和tableB，tableA不存在的记录显示tableA相关字段为NULL；

015 修改列定义，定义索引

ALTER TABLE table_name modify column_name varchar(32);
ALTER TABLE table_name ADD INDEX `column_name` (`column_name`);

016 make mysql

http://dev.mysql.com/doc/internals/en/cmake-build-options-official-mysql.html

 

• Unix (Makefiles)

  mkdir bld
  cd bld
  cmake .. -DBUILD_CONFIG=mysql_release
  make
  

注：安装libaio后如果还是提示依赖，重新执行上面步骤（删除bld）

017 mysql升级后无法赋用户权限，修正

2014-05-27 14:48:34 17291 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it
2014-05-27 14:48:34 17291 [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
2014-05-27 14:48:34 17291 [Warning] Info table is not ready to be used. Table 'mysql.slave_relay_log_info' cannot be opened.
2014-05-27 14:48:34 17291 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure
2014-05-27 14:48:34 17291 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
2014-05-27 14:48:34 17291 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
2014-05-27 14:48:34 17291 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
2014-05-27 14:48:34 17291 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_host_by_event_name' has the wrong structure
2014-05-27 14:48:34 17291 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure
2014-05-27 14:48:34 17291 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure
2014-05-27 14:48:34 17291 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_user_by_event_name' has the wrong structure
2014-05-27 14:48:34 17291 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_account_by_event_name' has the wrong structure

 

解决办法：
mysql_upgrade -u root -p

 

018  sql_mode doesn't have a default value null 

现象是无法导入存储过程，提示如题所示的错误，解决办法，修改配置文件中sql_mode，注释掉重新启动MySQL

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

如果发现执行存储过程时有默认值的情况仍旧无法插入数据，也可能是这个原因，这时要直接修改mysql.proc表中对应存储过程的sql_mode为NO_ENGINE_SUBSTITUTION即可

详细信息查看这里

也可以直接连接到mysql执行source /path/to/sql_file.sql

 

019 MySQL安装后没有mysql数据库

运行安装目录中的./scripts/mysql_install_db

020 mysql连接localhost

安装mysql后，只能使用localhost作为host连接，无法使用其他远程主机连接，纠结许久（防火墙好像没有限制啊！），发现mysqld是以13306端口启动的，但应用server的数据库配置中写的是3306，仍然连接成功。为什么？

原因是mysql连接localhost使用的是socket文件，而不是tcp连接，因此指定端口是无效的。如下所示的13306端口会被忽略

 

shell> mysql --port=13306 --host=localhostmysql --port=13306 --host=localhost

如果需要使用TCP连接，则需要指定--host为不同于localhost，或指定以TCP方式连接。

 

 

shell> mysql --port=13306 --host=127.0.0.1
shell> mysql --port=13306 --protocol=TCPmysql --port=13306 --host=127.0.0.1
shell> mysql --port=13306 --protocol=TCP

 

021 创建数据库

 

CREATE DATABASE IF NOT EXISTS vwvo default charset utf8 COLLATE utf8_general_ci;

#支持emonj字符

CREATE DATABASE IF NOT EXISTS vwvo1 default charset utf8mb4 COLLATE utf8mb4_general_ci;

参考：https://mathiasbynens.be/notes/mysql-utf8mb4

022 事务隔离级别

select @@global.tx_isolation,@@tx_isolation;