MySQL 5.7 常用操作

1、登录，查询数据

mysql -u root -p
show databases;
use information_schema;
show tables;
desc TABLES;
select * from TABLES;

 2、修改密码

-- 登录后执行
SET PASSWORD = PASSWORD('newpassword');

 3、mysql根据配置文件会限制server接受的数据包大小。有时候大的插入和更新会受max_allowed_packet 参数限制，导致写入或者更新失败。

-- 1.
show VARIABLES like '%max_allowed_packet%';
-- 2.
set global max_allowed_packet = 2*1024*1024*10;
-- 3.
重启mysql
-- 4.
show VARIABLES like '%max_allowed_packet%';

 4、mysql启停

#MySQL服务器开启，关闭，重启，查看状态  
/etc/init.d/mysql start  
/etc/init.d/mysql stop  
/etc/init.d/mysql restart  
/etc/init.d/mysql status

 5、将字符编码设置为UTF-8

cd /etc/mysql/mysql.conf.d
vi mysqld.cnf 
	#在[mysqld]标签下，增加服务器端的字符编码,增加[client]修改客户端编码
	[mysqld]
	character-set-server=utf8
	collation-server=utf8_general_ci
	[client]
	default-character-set=utf8
/etc/init.d/mysql restart

 6、让MySQL服务器被远程访问

cd /etc/mysql/mysql.conf.d
vi mysqld.cnf 
	#修改bind-address127.0.0.1为0.0.0.0
	#bind-address            = 0.0.0.0
mysql -u root -p
	mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "ROOT";  --ROOT为密码
	mysql> flush privileges;
/etc/init.d/mysql restart

 7、建立用户

# 新建book用户，密码为book，允许book可以远程访问abc数据库，授权book对abc进行所有数据库
mysql> GRANT ALL ON abc.* to book@'%' IDENTIFIED BY 'book';
#允许book可以本地访问abc数据库，授权book对abc进行所有数据库
mysql> GRANT ALL ON abc.* to book@localhost IDENTIFIED BY 'book';

 8、改变数据存储位置

cd /etc/mysql/mysql.conf.d/
vi mysqld.cnf
	[mysqld]
	datadir         = /var/lib/mysql

 9、修改最大连接数

show variables like 'max_connections'
set global max_connections=1000;

 10、修改连接超时时间

show global variables like 'wait_timeout';
set global wait_timeout =100000;