MySQL 5.7 常用操作
程序员文章站
2022-07-12 18:22:06
...
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;
上一篇: hibernate的session简介
下一篇: 使用PHP 给网站 生成快照图片和缩略图