One article summarizes Mysql database applications and common problems

1. Install mysql

1, CentOS system

1.) Install mysql

Download and install the repo source of mysql

$ wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
$ sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm

Install mysql

$ sudo yum install -y mysql-server

2.) Start/restart/shut down the service

service mysqld start | restart | stop

2. Alpine system

The mysql installed in the alpine system is actually the open source MariaDB. The MariaDB database is a branch/derivative of MySQL. It is fully compatible with MySQL and has improved functions, storage engines and some new functions. All are better than MySQL, installation reference

$ apk update

# 安装数据库及客户端
$ apk add mysql mysql-client

# 初始化数据库
$ mysql_install_db --user=mysql --datadir=/var/lib/mysql

# 启动服务
$ rc-service mariadb start  # 若没有rc，安装：apk add openrc

# 修改密码
$ mysqladmin -u root password '新root密码'

# 加入开机启动
$ rc-update add mariadb default

2. Operation mysql

1. Log in to the database

Default initialization direct login

$ mysql

If the prompt fails, explain If you have a password, run:

$ mysql -uroot -p

# 一次性登录
$ mysql -u用户 -p密码

2. Operation database

# 查看所有数据库
mysql> show databases;

# 创建数据库
mysql> create database xxx charset=utf8;

# 删除数据库
mysql> drop database xxx;

# 切换数据库
mysql> use mysql;

# 查看表
mysql> show tables;

# 创建表
mysql> create table xxx (
    id int,
    name varchar(20),
    update_time datetime
);

# 删除表
mysql> drop table xxx;

# 显示表结构
mysql> desc xxx;

# 查询指定a,b字段的记录,不知道用*代替
mysql> select a,b form xxx;

# 插入记录
mysql> insert into xxx(id,user) values(1,'wang',now());

# 删除记录
mysql> delete from xxx where name='wang';

# 清屏命令
mysql> system clear;

# 退出
mysql> quit;

3. User management

The user table of mysql is used to store all user permissions, where the host field represents the specified IP Users can use it. Two users have the same name but different hosts. Common host values ​​are as follows:

localhost: can only be used on the server side
192.168.4.%: the specified ip segment can be used
% : Wildcard, indicating that all IP users can use it, mostly used for remote connections

When creating/deleting users below, you can specify it through user@'host'. If not specified, the default is %

# 切换到mysql权限数据库
mysql> use mysql;

# 查看用户及所属的host
mysql> select user,host from user;

# 新建用户并设置密码
mysql> create user '用户名' identified by '密码';

# 上面不指定host，默认%，等同于：
mysql> create user '用户名'@'%' identified by '密码';

# 删除用户(只删除host为%的用户)
mysql> drop user '用户名';

# 查看用户权限
mysql> show grants for '用户名';

# 设置权限，并指定数据库
mysql> grant all privileges on xxxDB.* to '用户名';

# 修改密码，注意密码处不能直接password='新密码'
mysql> update user set password=password('新密码') where user='用户名';

# 刷新权限表
mysql> flush privileges;

4. Batch operation

1.) Batch execution of sql scripts

$ mysql -uroot -p -Dxxx < ./init.sql    # xxx为数据库

2.) Backup/restore database

$ mysqldump --all-databases -h127.0.0.1 -u root -p > ./backup/mysql-bak.sql
$ mysqldump --all-databases -h127.0.0.1 -u root -p < ./backup/mysql-bak.sql

三、FAQ

1. How to erase the root password if you forget it?

$ mysql –skip-grant-table &
  mysql> use mysql;
  mysql> update user set password=password('新密码') where user='root';
  mysql> flush privileges;
  mysql> quit;

What needs to be noted here is that each command needs to end with a semicolon ";". After performing the above operations, the root password will be cleared.

2. Null user error

ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql' Error:

Reason :

In the user table of the mysql database, there is an account with a blank user name, that is, an anonymous account. As a result, although root is used when logging in, the login is actually anonymous.

Solution:

# 1.关闭mysql
$ service mysqld stop

# 2.屏蔽权限
$ mysqld_safe --skip-grant-table # 屏幕出现： Starting demo from .....

# 3.新开起一个终端输入
$ mysql -uroot mysql
  mysql> update user set password=password('新密码') where user='root';
  mysql> flush privileges;
  mysql> quit;

3. Root can be used on the service, but remote login cannot be done

$ mysql -uroot -p
  mysql> use mysql;
  
  # 先查看user表host字段，有无通配符'%'，若有直接运行flush privileges;
  mysql> select host from user where user='root';  
  mysql> grant all privileges *.* to 'root'@'%' identified by 'root密码';
  mysql> flush privileges;
  mysql> quit;

4. Remote connection prompts caching_sha2_password error

After mysql5.7 version, the caching_sha2_password verification method is adopted by default

mysql> use mysql;
mysql> alter user 'root'@'%' identified with mysql_native_password by 'root密码';

5. Mysql changes the default data storage

$ mysqladmin -u root -p variables | grep datadir  # 查看mysql数据库存放目录
$ service mysqld stop
$ mv /var/lib/mysql /路径  # 移动数据库文件

$ vi /etc/my.cnf

# 修改datadir和socket两个字段，并添加以下：
[mysql] 
socket=/路径/mysql.sock

$ service mysqld start