A preliminary study on centos mysql-configuration, basic operations and problems

table of Contents:

centos install mysql
Simple operation using mysql client
python2 and python3 connect to mysql
mysql import file problem
Deadlock solution
Windows 7 remote connection to mysql server

1、 centos install mysql

Verify that there is mysql: rpm -qa | grep mysql

Download the rpm on the official website https://dev.mysql.com/downloads/repo/yum/, pay attention to download the first one:

wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm

After downloading to the local, start yum installation:

rpm -ivh mysql57-community-release-el7-9.noarch.rpm
yum install mysql-server

Start mysql (note that only users with administrator rights can start it):

sudo systemctl start mysqld

Similarly, stopping mysql can only be stopped with administrator privileges:

sudo service stop mysqld

View the running status of mysql:

systemctl status mysqld

Verify the installation of mysql:

mysqladmin --version

2、 Simple operation using the client###

Open mysql, there are some problems here, when you enter mysql:

ERROR 1045(28000): Access denied for user 'fangying'@'localhost'(using password: NO)

Solution:

Find the password:

cat /var/log/mysqld.log  | grep password

Then log in with this password: (-h can be omitted when localhost, and the rest of ip cannot be omitted)

/usr/bin/mysql -h localhost -u root -p
Enter password: 
Welcome to the MySQL monitor. Commands end with; or \g.
Your MySQL connection id is 11
Server version:8.0.11

Before viewing the table, you will be asked to reset your password:

mysql> show databases;
ERROR 1820(HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> alter user 'root'@'localhost' identified by 'your password';
Query OK,0 rows affected(0.04 sec)

At this time, it is enough to view the representation again:

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+4 rows inset(0.00 sec)

3、 python2 and python3 connect mysql

Python2 can connect to mysql through the MySQLdb module, and python3 can connect to mysql through pymysql:

Note that there is a problem here: the user name and password of mysql can be connected to the mysql client. When python3 is connected, it always prompts:

OperationalError:(1045,"Access denied for user 'root'@'localhost' (using password: NO)")

I searched a lot of information, and realized that the reason for this error prompt should be that the password was not found, such as the password was not entered. This confusion made me search for a long time and could not find a solution, because in fact this prompt is not clear enough!

The error message when using the MySQLdb module in python2 to connect to mysql is:

password' cannot be loaded:/usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory")

When I used this error prompt to find a solution, I found it immediately: Create a user with mysql_native_password password (although it is not exactly the same problem, but the problem is indeed solved)

CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'pMQiEge1ikst7S_6tlXzBOmt_4b';
grant all on *.* to 'root'@'%';

The problem is that the default authentication method of MySQL8.0.11 version is caching_sha2_password (and mysql_native_password in MySQL5.7 version). In order to modify the default authentication method (when creating a password later), you can modify the configuration file /etc/my.cnf:

another:

Installation of MySQLdb module in centos7 python2:

yum install MySQL-python
import MySQLdb

4、 mysql import problem###

1 ) Import local files into mysql

To import a local file into mysql, you need to specify the parameter local_infile to be turned on, and to do this, you need to compile the source code during installation and specify --enable-local-infile. The default is not to start (I installed Did not do so at the time):

. /configure --prefix=/usr/local/mysql --enable-local-infile
make install

So this method will not work;

If you didn't do this when compiling, and you don't want to recompile, you can add --local-infile when connecting to the database:

mysql -u echo -p --local-infile

At this time, load data local instead of load data should be used when importing data (this will cause an error):

load data local infile '/home/fangying/GAna/aod_csv/aod_all_2015_1.csv' into table example fields terminated by ',' ignore 1 lines;

Or add local-infile=1 to both [mysql] and [mysqld] of the configuration file /etc/my.cnf, and then you can import local files without adding --local-infile when logging in.

If there is a null value, you need to change the null value to'\n', otherwise the null value will be set to 0 for numeric types, and the null value will be set to 0000-00-00 for dates. You can also import as follows Makes: (If you want to show warnings: --show-warnings)

load data local infile '/home/fangying/GAna/aod_csv/aod_all_2015_1.csv' into table example2 fields terminated by ',' ignore 1lines(id,x,y,@vaod_value,ele,prs,tem,rhu,merra2,date_,win_avg)set aod_value=nullif(@vaod_value,'');

Solved problem 1:

When importing local files into mysql, an error occurred:

ERROR 1290(HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

The reason for this error is the setting of the --secure-file-priv parameter. The secure_file_priv parameter is used to limit the specified directory to which LOAD DATA, SELECT …OUTFILE, LOAD_FILE() are passed.

When secure_file_priv is NULL, it means that mysqld is restricted to not allow import or export.
When secure_file_priv is /tmp, it means that mysqld can only be imported and exported in the /tmp directory, and cannot be executed in other directories.
When secure_file_priv has no value, it means that mysqld is not restricted from importing and exporting in any directory.

Check this value:

show global variables like 'secure_file_priv'
     Value                 Variable_name
0 /var/lib/mysql-files/    secure_file_priv

So we need to modify the value of this parameter, open the configuration file /etc/my.cnf, and add:

secure_file_priv=''

Check again that the variable value has become ``

Solved problem 2:

You can already load data local locally, but you can't do this on jupyter:

This is because the mysql kernel added by my jypyter is a program written by others on git directly. In this program, pymysql is used to connect to mysql, and only commonly used parameters (host, user, password, port, charset) can be specified , The other parameters are not considered, so the parameter local_infile=1 cannot be added (you can modify the source code), so I won’t do it if it is troublesome.

When python connects to mysql, as long as local-infile=1 is specified, local data local can be used during operation. So the problem still lies in the local infile parameter.

But what I don't understand is that I only specified - local-infile once when connecting to mysql on the local client. The subsequent connections do not specify this parameter, and local data can also be imported. Why?

2 ) Import the file on the server into mysql

Using load data, you can only upload files in the location specified in datadir in the configuration file (/var/lib/mysql) and cache location (/tmp), and files in other locations will not work:

load data infile '/var/lib/mysql/a.txt' into table example fields terminated by ',' ignore 1 lines;

You can modify the value of datadir, which is a bit troublesome.

Note: The cache location is mentioned here. In fact, load data originally reads the data to the tmp location.

5、 Deadlock solution###

The following error message appears during operation:

1205,' Lock wait timeout exceeded; try restarting transaction'

Find deadlock and kill:

select *from information_schema.innodb_trx

kill 22

Perform the kill operation directly in mysql.

6、 windows7 remote connection to mysql server###

1 ) Create a user who allows the host with ip 192.168.. to access and authorize on the mysql server of centos: (in mysql 8 must be created before authorization, pay attention to the authorization of the user)

create user 'username'@'192.168.***.***' identified by 'your password'；
grant all privileges on *.* to [email protected].***.***;

Note: The meaning of creating a user here is that the username user of the host whose ip is 192.168..** can access the mysql server and have these permissions. **

2 ) Open the port occupied by mysql in centos:

sudo firewall-cmd --zone=public--add-port=3307/tcp --permanent

3 ) Just connect in the windows 7 command line or workbench:

mysql -h 192.168.***.***-u username -p

In the workbench, database-connect database is fine.