Shulou(Shulou.com)05/31 Report--

This article mainly introduces the relevant knowledge of "What are the common problems in Mysql database application". Xiaobian shows you the operation process through actual cases. The operation method is simple and fast, and the practicality is strong. I hope this article "What are the common problems in Mysql database application" can help you solve the problem.

I. Install mysql1. CentOS system

1.）install MySQL

Download and install repo source for 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 service

service mysqld start | restart |stop2. alpine system mysql installed in alpine system is actually open source MariaDB. MariaDB database is a branch/derivative version of MySQL, fully compatible with MySQL, and stronger than MySQL in terms of extended functions, storage engine and some new functional improvements. Installation Reference $ apk update#Install database and client $ apk add mysql mysql-client#Initialize database $ mysql_install_db --user=mysql --datadir=/var/lib/mysql#Start service $ rc-service mariadb start #If there is no rc, install: apk add openrc#modify password $ mysqladmin -u root password 'new root password'#join boot startup $ rc-update add mariadb default II, operation mysql1, login database default initialization direct login $ mysql If the prompt fails, indicating that there is a password, run: $ mysql -uroot -p#one-time login $ mysql -u user-p password 2. operate database #view all databases mysql> show databases;#create database mysql> create database xxx charset=utf8;#delete database mysql> drop database xxx;#switch databases mysql> use mysql;#view tables mysql> show tables;#create tables mysql> create table xxx ( id int, name varchar(20), update_time datetime);#delete table mysql> drop table xxx;#display table structure mysql> desc xxx;#query records specifying fields a,b, do not know to replace mysql> select a,b form xxx with *;#insert records mysql> insert into xxx(id,user) values(1,'wang',now());#delete records mysql> delete from xxx where name='wang';#clear screen command mysql> system clear;#exit mysql> quit;3, user management

mysql user table is used to store all user permissions, where the host field indicates that the specified ip user can use, the same name different host for two users, host common values are as follows:

localhost: only available on server

192.168.4.%：Specify ip segment available

%: Wildcard, indicating that all ip users can use, mostly used for remote connections

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

#Switch to mysql permission database mysql> use mysql;#View users and their hosts mysql> select user,host from user;#Create user and set password mysql> create user 'username' identified by 'password';#Do not specify host above, default %, equivalent to: mysql> create user ' username '@'%' identified by ' password';#Delete user (Delete only users with host %)mysql> drop user 'username';#view user permissions mysql> show grants for 'username';#set permissions and specify database mysql> grant all privileges on xxxDB.* to 'user name';#change password, note that password cannot be directly password=' new password 'mysql> update user set password=password ('new password') where user=' user name';#refresh permission table mysql> flush privileges;4. batch operation

1.）sql script batch execution

$ mysql -uroot -p -Dxxx

./ backup/mysql-bak.sql$ mysqldump --all-databases -h227.0.0.1 -u root -p

use mysql; mysql> update user set password=password ('new password') where user='root'; mysql> flush privileges; mysql> quit; Note that each command needs to end with a semicolon ";", after performing the above operations, the root password is cleared. 2. Empty user error

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

Reason:

In the user table of mysql database, there is an account with empty user name, i.e. anonymous account, which causes the login to be anonymous although root is used.

Treatment scheme:

# 1. close mysql$ service mysqld stop# 2. block permissions $ mysqld_safe --skip-grant-table #screen appears: Starting demo from...# 3. Start a new terminal and enter $ mysql -uroot mysql mysql> update user set password=password ('new 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; #First check the host field in the user table for wildcard '%'. If there is any, run flush privileges directly; mysql> select host from user where user=' root'; mysql> grant all privileges *.* to 'root'@'%' identified by 'root password'; mysql> flush privileges; mysql> quit;4. remote connection prompt caching_sha2_password error Since mysql version 5.7, the default authentication method caching_sha2_password has been adopted mysql> use mysql;mysql> alter user 'root'@'%' identified with mysql_native_password by 'root password'; 5. mysql modifies the default data storage $ mysqladmin -u root -p variables| grep datadir #View mysql database storage directory $ service mysqld stop$ mv /var/lib/mysql /path #Move database file $ vi /etc/my.cnf#Modify datadir and socket two fields, and add the following: [mysql] socket=/path/mysql.sock$ service mysqld start About "Mysql database application common problems What" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the industry information channel. Xiaobian will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.