Linux中使用MySQL的命令

# 安装MySQL客户端
apt install mysql-client

# 安装MySQL服务端
apt install mysql-server

# 启动MySQL
/etc/init.d/mysql start

# 初始化MySQL
sudo mysql_secure_installation

# 安装MySQL客户端
apt install mariadb-client

# 安装MySQL服务端
apt install mariadb-server

# 启动MySQL
/etc/init.d/mysql start

# 初始化MySQL
sudo mysql_secure_installation

# 安装客户端和服务端 默认root密码为空
yum install mariadb-server

# 启动 mariadb
sudo systemctl start mariadb

# 设置开机自启
sudo systemctl enable mariadb

# 查看mariadb状态
sudo systemctl status mariadb

# 初始化MySQL
sudo mysql_secure_installation

# 允许root外部访问连接
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '这里是root密码' WITH GRANT OPTION;

# 刷新权限
FLUSH PRIVILEGES; 

mysql> select user, host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | %         |
| root | 127.0.0.1 |
| root | ::1       |
| root | localhost |
+------+-----------+

# 删除 host 为 % 的用户
DELETE FROM mysql.user WHERE User= "root" and Host="%";

# 刷新权限
FLUSH PRIVILEGES; 

mysql> select user, host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
| root | localhost |
+------+-----------+

# 修改 root 密码的 SQL语句
use mysql;
set password for 'root'@'localhost' = password('你设置的密码');

# 刷新权限 并退出
flush privileges;
quit; 

mysqladmin -uroot -poldpassword password newpassword

mysql -h目标IP地址 -u用户名 -p密码 -P端口
mysql -h10.211.55.9 -uroot -ppassword -P 33060

# 导出所有数据库
mysqldump -uroot -proot --all-databases >/tmp/all.sql

# 导出 db1、db2 两个数据库的所有数据
mysqldump -uroot -proot --databases db1 db2 >/tmp/user.sql

# 导出 db1 中的 a1、a2 表
mysqldump -uroot -proot --databases db1 --tables a1 a2  >/tmp/db1.sql

# 导出 db1 表 a1 中 id=1 的数据
mysqldump -uroot -proot --databases db1 --tables a1 --where='id=1'  >/tmp/a1.sql

# 只导出表结构不导出数据
mysqldump -uroot -proot --no-data --databases db1 >/tmp/db1.sql

mysql -uroot -proot < databases.sql

CREATE USER 'test'@'%' IDENTIFIED BY 'test用户的密码';
GRANT ALL PRIVILEGES ON `test`.* TO 'test'@'%';