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.
Installing mysql on Ubuntu is almost always installed automatically. You can choose to install php/apache2 during the installation process.
Want mysql to play 6, mysql command line must be used, or sql must be, review it together.
View the database:
Use the database:
Delete the database:
View the current database table:
Create a data table:
View the table structure:
Drop the table (DROP TABLE statement):
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)
change the data:
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.
Description: Query the first n results.
Description: The result after querying m rows.
Description: % means fuzzy query, %php means all results ending with php, %php% means all results containing php.
Query the result that the address field is not empty.
Query the result of age between 0-18.
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 *.
Note: If you do not specify a sorting method when sorting fields, the default is ASC ascending.
Before using full-text search, you must first create a field index, for example, I want to query like this:
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:
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).
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.
There are three ways to query multiple tables: cross query, equivalent query, external query (left join, right join)
Cross-query can find all the data in the 2 tables, which is time consuming.
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.
When we just installed mysql on the server, the default is to log in without a password.
Of course, we definitely have to set a password for mysql, so how to set up the most convenient?
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.
Restart mysql, you should be able to log in using the root account with the password set.
What if I forget the mysql password? Nothing, you can enter safe mode and reset your password.
First, we stop the MySQL service:
Start MySQL in safe mode:
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:
Then reset your password:
After the reset is complete, we exit and start the MySQL service:
When I set up mysql on the server, enter the following command:
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:
The login failed. The reason is that mysql only supports local login by default, and does not support remote login.
The first step is to log in to mysql (server local login, because remote login is not available), view user table (built-in table)
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 %
Or change the host field content of the root user.
Change the password of the root user:
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):
Look at the contents of the user table:
You can see that a user nmask has been added to the user table, and host is %.
At this point, use the nmask user to remotely connect to 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.
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.
Give this user permission when adding it
If it is phpmyadmin, you can log in as the root user and enter the user table to modify it.
Finally restart Mysql:
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:
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.
Finally restart Mysql:
Connection error message:
Modify the contents of /etc/my.cnf:
Restart the mysql service:
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
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.
After installing phpmyadmin and apache, visit http://localhost/phpmyadmin to display 403.
Edit the phpmyadmin.conf file:
In addition, if you want to change the port of apache, you can change the /etc/httpd/conf/httpd.conf file.
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/
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.
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.
Insert into the following:
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.
- 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.
- 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.
Sometimes you will encounter some special characters when storing mysql (WeChat, qq expression, etc.), it will prompt the encoding error, similar to the following:
Note: You must change the above 4 codes to utf8mb4, so that you will not report an error.
When using Python development, it is often used in mysql-related operations, this time you need to use a third-party library, MySQLdb.
Connect mysql database
Create a table structure
Write data to the table
Write multiple rows of data to the table
Update data in the table
Query data in the table
Delete data in the table
Submit a commit
Close the connection
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 *