Shulou(Shulou.com)06/01 Report--

Note content:

13.4 mysql user Management

13.5 commonly used SQL statements

13.6 mysql database backup and recovery

Note date: 2017-10-31

13.4 mysql user Management

When setting up a website, the background application will connect to mysql, and you need a user password to connect to mysql, but you can't let it use root users. The permissions of root users are too high and insecure, so you need to create a user and grant this user some permissions. You can specifically grant some permissions to these users so that it can operate what it cannot.

Here's how to create a mysql user and authorize it:

Create a user with the following statement:

Grant all on *. * to 'user1'@'192.168.77.130' identified by' 123456a'

Common sense: this grant statement is not recorded in the command history because it is not safe.

Now that you have created a user1 user, because you specified the source IP when you created this user, you need to use-h to specify IP when logging in to this user:

If you want to create a mysql user that can connect locally, you can specify localhost at the time of creation:

Grant all on *. * to 'user1'@'localhost' identified by' 123456a'

Created as a user who can connect locally, you do not need to use-h to specify the source IP when logging in:

Using show grants;, you can see what permissions the current user has:

If you want to see a specific user, add the for user name @ login method after the command. Example:

Show grants for user1@'localhost'

Let's create another user and grant that user permission to operate on a specific library:

Now let's take a look at user2's permissions:

You can see the specific sql statements that user2 can execute, and which tables of which library can be manipulated.

When you want to increase the user's connection to IP or other permissions, you can directly copy the statements shown in the show grants command, and then change the IP. For example, I want to enable user2 to log in using the 192.168.77.139 IP:

Then take a look at show grants:

The original IP can also be connected, and this operation is to add an IP that can be connected:

13.5 commonly used SQL statements

The following is a brief introduction to several add, delete, query and modify statements commonly used in mysql:

Select count (*) from mysql.user

This statement looks at how many rows there are in the user table in the mysql library. Count is a function that counts the number of rows:

Select * from mysql.db\ G

View everything in the db table in the mysql library:

Select db from mysql.db

View all the contents of the db field in the db table in the mysql library:

Select db,user from mysql.db

View all the contents of the db and user fields in the db table in the mysql library:

Select * from mysql.db where host like '192.168%'\ G

The host field in the db table in the mysql library is 192.168. For everything at the beginning, like is the keyword of the fuzzy query, and where is a specified condition:

Insert into db1.t1 values (1, 'abc')

The insert statement is used to insert data:

Update db1.t1 set name='aaa' where id=1

Update statements are used to modify or update data:

Truncate table db1.t1

By emptying the data, you can empty all the data in a table:

Drop table db1.t1

Drop is used to delete structures. The deletion of drop not only deletes the data, but also deletes the structure. The above drop deletes the T1 table in the db1 library:

10. Drop database db1

This is to delete the database, but also to delete the structure:

Drop is different from truncate and delete statements in that the former deletes the entire structure, while the latter only deletes the data in the structure.

Keep in mind that drop and truncate, delete and other statements should not be used as far as possible, if you need to use them, remember to back up the data in advance, and when using it, be careful not to delete the wrong data. If you accidentally delete an online database, and there is no backup to restore, then you will be fired or compensated.

13.6 mysql database backup and recovery

Because of the importance of data, backup and recovery technology must be mastered. Here is an introduction to the operations related to backup and recovery of the database:

Mysqldump is a backup command that can back up a library or a table:

Backup library, the syntax is as follows:

Mysqldump-uroot-p123456 mysql > / tmp/mysql.sql

Restore the library and redirect the contents of the backup file to an empty database. The syntax is as follows:

Mysql-uroot-p123456 mysql2

< /tmp/mysql.sql 进入到mysql2中，可以看到数据已经恢复到这个库中了： 从以上备份和恢复数据库可见，其实就是一个重定向的应用，重定向出去叫备份，重定向进来叫恢复。 备份表，除了备份整个数据库之外，还能针对数据库中的某个表进行备份，指定库名和表名即可，语法如下： mysqldump -uroot -p123456 mysql user >

/ tmp/user.sql

You can use the less command to look at the user.sql file and find that it is full of sql statements:

Recover the data one by one through the sql statement.

Restore the table, just like the restore library, and redirect the contents of the backed up files back. The syntax is as follows:

Mysql-uroot-p123456 mysql

< /tmp/user.sql 备份所有的库，如果mysql中有很多数据库，一个个去备份太麻烦，所以需要能够备份所有数据库的语句，语法如下： mysqldump -uroot -p -A >

/ tmp/123.sql

-An is all, all meaning

You can see that this backup file is relatively large, with 1.3m:

Only backup the table structure, if you do not need data, you can only back up the table structure, using the-d option, the syntax is as follows:

Mysqldump-uroot-p123456-d mysql > / tmp/mysql.sql

Then you can use less to view the contents of the backup file, and you will find that there are no insert statements, only drop and create statements.

Mysqldump is only suitable for backups with a small amount of data. If the database in the database is very large, it is not suitable to use mysqldump. If you use mysqldump, the backup will be very slow. For more information on how to back up a large number of databases, please refer to the following extension:

Expansion

SQL sentence tutorial

Https://blog.51cto.com/zt/206

What is a transaction? What are the characteristics of transactions?

Http://blog.csdn.net/yenange/article/details/7556094

Recover data for a specified period of time according to binlog

Http://www.centoscn.com/mysql/2015/0204/4630.html

Mysql character set adjustment

Http://xjsunjie.blog.51cto.com/999372/1355013

Database innobackupex backup of the innodb engine using xtrabackup backup Xtrabackup incremental backup:

Http://zhangguangzhi.top/2017/08/23/innobackex%E5%B7%A5%E5%85%B7%E5%A4%87%E4%BB%BDmysql%E6%95%B0%E6%8D%AE/#%E4%B8%89%E3%80%81%E5%BC%80%E5%A7%8B%E6%81%A2%E5%A4%8Dmysql

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.