Mysql related notes

Take control of your own destiny
Destiny is in your hands

Recently, due to project needs, the mysql database was specially studied. Although I have studied mysql during the university, I have been somewhat unfamiliar with mysql because I have been using rethinkdb and mongodb databases before. I have encountered a lot of pits during my recent use, but fortunately I finally solved all the problems with a powerful Google. So here are some notes on mysql related issues, which may involve MySQL security related issues, such as using mysql to export shells, mysql rights, etc., right to backup.

Install Mysql

for ubuntu

1
2
sudo apt-get install mysql-server
sudo apt-get install phpmyadmin

Installing mysql on Ubuntu is almost always installed automatically. You can choose to install php/apache2 during the installation process.

for centos

1
2
3
4
5
wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm
yum install mysql-community-server
yum install phpmyadmin
yum install httpd

Mysql basic commands

Want mysql to play 6, mysql command line must be used, or sql must be, review it together.

Database Operations

View the database:

1
show databases;

Use the database:

1
Use database name;

New database:

1
CREATE DATABASE mydb;

Delete the database:

1
DROP DATABASE mydb;

Database Table Operations

View the current database table:

1
show tables;

Create a data table:

1
2
3
4
5
6
7
8
CREATE TABLE teacher(
id int primary key auto_increment,
name varchar(20),
gender char(1),
age int(2),
birth date,
description varchar(100),
);

View the table structure:

1
Desc table name;

Drop the table (DROP TABLE statement):

1
DROP TABLE teacher;

Note: The drop table statement deletes all of the records and table structures

Modify the table structure (ALTER TABLE statement):

  • alter table test add column job varchar(10); –Add table column
  • alter table test rename test1; – modify the table name
  • alter table test drop column name; – delete table column
  • alter table test modify address char(10) – modify the table column type (change type)
  • alter table test change address address1 char(40) – modify the table column type (change the name and type, the same as the following line)
  • alter table test change column address address1 varchar(30)–Modify table column name (change name and type)

Data manipulation

adding data:

1
2
3
```
Query data:
```bash

change the data:

1
UPDATE table name SET field 1 name = value, field 2 name = value, field 3 name = value where field name = value;

delete data:

1
2
3
4
5
6
7
```
*The above commands are the most basic, but also the most commonly used*
## Common Sql statement
### Get a fixed number of results
```bash
select * from table limit m,n

Description: where m is the index at the beginning of the record, starting from 0, indicating the first record; n means starting from the m+1th entry, taking n.

1
select * from table limit 0,n

Description: Query the first n results.

1
select * from table limit m,-1

Description: The result after querying m rows.

Query string

1
SELECT * FROM table WHERE name like '%PHP%'

Description: % means fuzzy query, %php means all results ending with php, %php% means all results containing php.

Non-empty query

Query the result that the address field is not empty.

1
SELECT * FROM table WHERE address <>''

Judging the query

Query the result of age between 0-18.

1
SELECT * FROM table WHERE age BETWEEN 0 AND 18

Number of query results

1
select count(*) from table

Query results do not display duplicate records

1
SELECT DISTINCT Field Name FROM Table Name WHERE Query Condition

Note: DISTINCT in the SQL statement must be used in conjunction with the WHERE clause, otherwise the output information will not change, and the field cannot be replaced with *.

Query sort

1
2
SELECT field name FROM tb_stu WHERE condition ORDER BY field DESC descending
SELECT field name FROM tb_stu WHERE condition ORDER BY field ASC ascending

Note: If you do not specify a sorting method when sorting fields, the default is ASC ascending.

Multi-condition query sorting

1
SELECT field name FROM tb_stu WHERE condition ORDER BY field 1 ASC field 2 DESC

Before using full-text search, you must first create a field index, for example, I want to query like this:

1
match(`name`,`name2`) aganst("abc" IN BOOLEAN MODE)

That is, to query the record with the string abc in the name or name2 field, you need to combine the name and name2 fields in advance, similar to this:

1
select * from a left join b on a.pid = b.pid WHERE MATCH(`id`,`ip`) AGAINST("abc" IN BOOLEAN MODE) OR MATCH(`name`,`port`) AGAINST("123" IN BOOLEAN MODE)

Description: The string abc can also be replaced by %s, parameterized to construct sql statement. It is worth noting that by default mysql only supports full-text indexing of more than 4 characters. That is, searching for “nginx” is fine, but searching for “tcp” will not work. The solution is to modify the /etc/my.conf configuration file, add a line ft_min_word_len = 2, then restart mysql and rebuild the index (but my test failed).

Full-text search fuzzy matching and exact matching

When we search for: thief.one, if it is not included in the double quotes, it will match the result of thief, one, because the default will use . to split the string, the search becomes a string with thief or one, so accurate The search for: “thief.one” can be solved because it is treated as a complete string.

Multi-table joint query

There are three ways to query multiple tables: cross query, equivalent query, external query (left join, right join)
Reference: http://blog.csdn.net/hguisu/article/details/5731880

Cross-connect query

Cross-query can find all the data in the 2 tables, which is time consuming.

1
2
3
SELECT * FROM table1 CROSS JOIN table2
SELECT * FROM table1 JOIN table2
SELECT * FROM table1,table2

Equivalent join query

1
SELECT * FROM table1 INNER JOIN table2

External connection query

This kind of query is the most commonly used, find out the public in the a and b tables, and find that only a table or b table is unique.

1
2
select id, name from user left join techer on user.id = teacher.id
select id, name from user right join techer on user.id = teacher.id

1
select id, name from user left join techer on user.id = teacher.id left join home on teacher.id=home.id

Export database to sql file

1
mysqldump -uusername -ppassword db_name > file_name.sql

Mysql usage permission problem

mysql initialization setting password

When we just installed mysql on the server, the default is to log in without a password.

1
mysql -u root

Of course, we definitely have to set a password for mysql, so how to set up the most convenient?

1
2
3
4
>>use mysql;
>>update user set host = '%' where user = 'root';
>>UPDATE user SET Password=PASSWORD('nmask') where USER='root';
>>flush privileges;

Note: It should be noted here that after the above command is successfully entered, it is still impossible to log in with the root account password. Why? Because there are several default root accounts, this will affect the root account of setting the password. You need to delete the remaining root accounts.

1
2
>>delete from user where user='root' and host!='%';
>>flush privileges;

Restart mysql, you should be able to log in using the root account with the password set.

forget password? Safe mode

What if I forget the mysql password? Nothing, you can enter safe mode and reset your password.

First, we stop the MySQL service:

1
sudo service mysqld stop

Start MySQL in safe mode:

1
sudo mysqld_safe --skip-grant-tables --skip-networking &

Note that we added –skip-networking to avoid remote login to MySQL without a password. This way we can log in directly with root without a password:

1
mysql -u root

Then reset your password:

1
2
3
mysql> use mysql;
mysql> update user set password=PASSWORD("nmask") where User='root';
mysql> flush privileges;

After the reset is complete, we exit and start the MySQL service:

1
sudo service mysql restart

Reference: http://www.ghostchina.com/how-to-reset-mysqls-root-password/

Only mysql can be connected locally, can the remote machine not connect?

When I set up mysql on the server, enter the following command:

1
2
3
[root@ ~]# mysql -u root -p
Enter password:
mysql>

When you enter the mysql password, the mysql> prompt appears, indicating that you have successfully logged into mysql.

I am happy to open my own mac, ready to remotely connect to mysql on the server, the results are as follows:

1
2
3
[Mac~]mysql -u root -p -h 192.168.2.2
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'192.168.2.2' (using password: YES)

The login failed. The reason is that mysql only supports local login by default, and does not support remote login.

solution

The first step is to log in to mysql (server local login, because remote login is not available), view user table (built-in table)

1
2
3
4
5
6
7
8
9
10
11
Mysql> use mysql; #Select mysql database (mysql is the database name)
Mysql> select host, user, password from user; # (view the contents of the user table)
+-----------+------------+-------------------------------------------+
| host | user | password |
+-----------+------------+-------------------------------------------+
| localhost | root | *21D8392A6B4CA12B9D194ED3E245258C4BE56DBA |
| 127.0.0.1 | root | *930D8392A6B4CA12B9D194ED3E245258C4BE56DB |
+-----------+------------+-------------------------------------------+
5 rows in set (0.00 sec)
mysql>

As you can see, there is currently only one root user in the user table, and the host is 127.0.0.1/localhost, which means that the root user currently only supports local ip access connections.

The second step is to modify the contents of the table.

Add a user and set the host to %

1
mysql>CREATE USER 'nmask'@'%' IDENTIFIED BY '123456';

Or change the host field content of the root user.

1
mysql>update user set host = '%' where user = 'root';

Change the password of the root user:

1
UPDATE user SET Password=PASSWORD('nmask') where USER='root';

Note: When initializing the installation of mysql, the default may only log in as the root user, but the default root does not set a password, so you can first add a password to the root user to log in.

Flush (must be flushed to make it work):

1
mysql>flush privileges;

View user

1
mysql> select host,user from mysql.user;

Look at the contents of the user table:

1
2
3
4
5
6
7
8
9
mysql> select host,user,password from user;
+-----------+------------+-------------------------------------------+
| host | user | password |
+-----------+------------+-------------------------------------------+
| localhost | root | *21D8392A6B4CA12B9D194ED3E245258C4BE56DBA |
| 127.0.0.1 | root | *930D8392A6B4CA12B9D194ED3E245258C4BE56DB |
| % NMSK * 435 0711250895 SA 1 S 068 FDESEESB 477122
+-----------+------------+-------------------------------------------+
5 rows in set (0.00 sec)

You can see that a user nmask has been added to the user table, and host is %.

Restart Mysql:

1
sudo /etc/init.d/mysqld restart

At this point, use the nmask user to remotely connect to Mysql:

1
2
3
mysql -u nmask -p -h 192.168.2.2
Enter password:
mysql>

The connection is successful because the host content of this user is %, which means that any host is allowed to access this mysql service.

User permissions are very low

When I log in with the nmask account, I found that the permissions are very low. The specific performance is that I can only see the information_schema database.

solution

Give this user permission when adding it

1
2
3
4
mysql>INSERT INTO user
-> VALUES('%','nmask',PASSWORD('123456'),
-> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql>flush privileges;

or

1
2
3
mysql>CREATE USER 'nmask'@'%' IDENTIFIED BY '123456';
mysql>GRANT ALL PRIVILEGES ON *.* TO 'nmask'@'%' WITH GRANT OPTION;
mysql>flush privileges;

If it is phpmyadmin, you can log in as the root user and enter the user table to modify it.

Finally restart Mysql:

1
sudo /etc/init.d/mysqld restart

Custom Authorization Issues

If you want nmask to use the 123456 password to connect to the mysql server from any host, other passwords will not work, you can:

1
2
mysql>GRANT ALL PRIVILEGES ON *.* TO 'nmask'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
mysql>flush privileges;

If you want to allow the user nmask to connect to the mysql server only from a host with ip 10.0.0.1, you can only use 123456 as the password.

1
2
mysql>GRANT ALL PRIVILEGES ON *.* TO 'nmask'@'10.0.0.1' IDENTIFIED BY '123456' WITH GRANT OPTION;
mysql>flush privileges;

Finally restart Mysql:

1
sudo /etc/init.d/mysqld restart

Can only connect to localhost?

Connection error message:

1
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.10.2' (111) Cannot connect with 192.168.10.2.

solution

Modify the contents of /etc/my.cnf:

1
Bind_address=127.0.0.1 is changed to bind_address=192.168.10.2

Restart the mysql service:

1
sudo /etc/init.d/mysqld restart

Pit the cheats: After modifying the content through the mysql command line, remember to addh; if it does not work, try restarting the mysql service

Generally, the default maximum connection number of mysql is 100. When the number of mysql connections exceeds this, the error will be reported; the solution can change the /etc/my.cof file and change the maximum connection limit.
Add max_connections=N to [mysqld]. If you don’t have this file, copy the *.cnf file you need from the support-files folder in the compiled source to /etc/my.cnf

1
2
3
4
5
6
7
8
9
10
11
12
13
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 160M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
max_connections=1000

mysql service restart error

If the mysql restart fails, you can check the log first and check the specific error in /var/log/mysql.log.
In general, it may be a permission problem. If mysql is a mysql user right, you need to switch to the sudo su mysql user to start the mysql service.

phpmyadmin 403 question

After installing phpmyadmin and apache, visit http://localhost/phpmyadmin to display 403.

1
sudo vim /etc/httpd/conf.d/phpMyAdmin.conf

Edit the phpmyadmin.conf file:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<Directory /usr/share/phpMyAdmin/>
AddDefaultCharset UTF-8
<IfModule mod_authz_core.c>
# Apache 2.4
<RequireAny>
#Require ip 127.0.0.1
#Require ip ::1
Require all granted
</RequireAny>
</IfModule>
<IfModule !mod_authz_core.c>
# Apache 2.2
Order Deny,Allow
Deny from All
Allow from 127.0.0.1
Allow from ::1
</IfModule>
</Directory>

In addition, if you want to change the port of apache, you can change the /etc/httpd/conf/httpd.conf file.

Mysql performance optimization

mysql insert acceleration

Insert is the most common action for manipulating a database. When there is a large amount of data to be inserted into the database, performance is critical, that is, the speed at which data is inserted. Mysql insert performance optimization reference: http://blog.jobbole.com/29432/

Scenario: Inserting multiple pieces of data into one SQL statement (pro-test valid)

Modify the sql statement to the following type, that is, a SQL statement inserts multiple pieces of data, which can greatly improve the insertion efficiency.

1
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1);

The modified insert operation can improve the insertion efficiency of the program. There are two main reasons for the high efficiency of the second SQL execution. One is to reduce the operation of SQL statement parsing. It only needs to parse once to insert data. The second is that the SQL statement is shorter, which can reduce the IO of network transmission.

Scenario: Insert processing in a transaction

Insert into the following:

1
2
3
4
5
START TRANSACTION;
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('1', 'userid_1', 'content_1', 1);
...
COMMIT;

Using transactions can improve the efficiency of data insertion. Because when doing an INSERT operation, MySQL internally creates a transaction and performs true insert processing within the transaction. By using transactions, you can reduce the cost of creating a transaction, and all inserts are committed before they are committed.

Precautions:

  1. The SQL statement has a length limit. The data merge must not exceed the SQL length limit in the same SQL. It can be modified by the max_allowed_packet configuration. The default is 1M.
  2. The transaction needs to control the size. Too large a transaction may affect the efficiency of execution. MySQL has an innodb_log_buffer_size configuration item. If this value is exceeded, the log will use the disk data. At this time, the efficiency will decrease. So it is better to commit the transaction before the transaction size reaches the configuration item data level.

Mysql special character encoding problem

Storage error

Sometimes you will encounter some special characters when storing mysql (WeChat, qq expression, etc.), it will prompt the encoding error, similar to the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
```
### Storage Special Encoding Solution
First of all, it is necessary to change the utf8 encoding of mysql to utf8mb4. utf8mb4 is a supplement to utf8, so that it can save some special characters.
#### Modify database encoding
You can select the database, then click on the action, the ordering rule is changed to: utf8mb4_general_ci
#### Modify data table encoding
Select the data table, click the operation, and modify the collation to: utf8mb4_general_ci
#### Modify data field encoding
Select the field, click Modify, and the ordering rule is changed to: utf8mb4_general_ci
#### Modify the code to connect mysql in the code:
Here is an example of python:
```bash
mysql_charset="utf8mb4"

Note: You must change the above 4 codes to utf8mb4, so that you will not report an error.

Python operation Mysql

When using Python development, it is often used in mysql-related operations, this time you need to use a third-party library, MySQLdb.

MySQLdb installation

1
sudo pip install mysql-python

or

1
sudo apt-get install python-mysqldb

Usage

Import module

1
import MySQLdb

Connect mysql database

1
cursor = conn.cursor()

Create a table structure

1
2
sql = "create table if not exists user(name varchar(128) primary key, created int(10))"
cursor.execute(sql)

Write data to the table

1
2
3
4
5
sql = "insert into user(name,created) values(%s,%s)"
param = ("aaa",int(time.time()))
n = cursor.execute(sql,param)
cursor.close()
Conn.commit() #must be committed, otherwise the data will only be cached locally, but will not be actually inserted into the database.

Write multiple rows of data to the table

1
2
3
sql = "insert into user(name,created) values(%s,%s)"
param = (("bbb", int (time.time ())), ("ccc", 33), ("ddd", 44))
n = cursor.executemany(sql,param)

Update data in the table

1
2
3
sql = "update user set name=%s where name='aaa'"
param = ("zzz")
n = cursor.execute(sql,param)

Query data in the table

1
2
3
4
5
n = cursor.execute("select * from user")
for row in cursor.fetchall():
print row
for r in row:
print r

Delete data in the table

1
2
3
sql = "delete from user where name=%s"
param = ("bbb")
n = cursor.execute(sql,param)

Delete table

1
2
sql = "drop table if exists user"
cursor.execute(sql)

Submit a commit

1
conn.commit()

Close the connection

1
conn.close()

Python operation mysql optimization problem

1, the commit operation is placed at the end, or outside the loop
2, use executemany, insert multiple data

@2017.07.28 Add mysql permission question content
@2017.07.29 Add mysql basic commands, mysql performance optimization, python operation mysql

  • This article will continue to add mysql related issues *

本文标题:Mysql related notes

文章作者:nmask

发布时间:2017年07月26日 - 14:07

最后更新:2019年08月16日 - 15:08

原始链接:https://thief.one/2017/07/26/01/en/

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

nmask wechat
欢迎您扫一扫上面的微信公众号,订阅我的博客!
坚持原创技术分享,您的支持将鼓励我继续创作!

热门文章推荐: