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