Backup and recovery of data and user Rights management

I. Backup and recovery of data

Copy the existing data and store it on a different server. MySQL provides a lot of backup, common text backup, data backup, SQL backup, etc.

1. Text Backup

Text backup is the simplest way to backup!

It is generally believed that text backup is only suitable for MyISAM storage engine data tables, that is, the data table three files (structure, data, index) to the other server on the line

Features of text backup:

Simple (copy and paste only), every time you need to back up all the files, very wasted disk space

2. Data backup

1) Backup syntax

Select *| Field List Into outfile file Address from Table name ;

SELECT * into outfile ' d:/backup/php_student.txt ' from php_student;

① first to have directory name (backup), directory name cannot be omitted

② Backup, you cannot specify an existing file store backup data, files can only be created automatically by the system

2) Restore syntax

Load Data infile file path into table Table name [ field List ];

Load data infile ' d:/backup/php_student.txt ' into table php_student;

Add data directly to the backup table and can be loaded back when you restore it.

The precondition is that the structure of the records in the document should be consistent with the structure of the table!

3. SQL backup

SQL backup is to back up the structure and data of a table in the form of an SQL statement. In MySQL, there is a client dedicated to backup: Mysqldump.exe

1) Backup syntax

mysqldump-hlocalhost–p3306-uroot–p database name [ datasheet 1 data sheet 2...] > Storage Path

If only the database name, no data table name, meaning is to back up the entire database! mysqldump–uroot–p php2016 php_student > D:/backup/php_student.sql

2) Restore syntax

There are two ways of doing this:

First: Using the MySQL client restore

The syntax form is:

Mysql-hpup Database name < Backup Path mysql–uroot–p php2016 < D:/backup/php_student.sql

The second type: Using SQL commands

Source Backup Path ;

Features of SQL Backup:

① can back up table structure, more secure

② Backup is a backup of the entire table, time-consuming, backup files are also relatively large, compared to waste disk space

In real projects, you can generally use the N-day rotation backup strategy! For example: Back up only the last 7 days of data

Second, user Rights management

Assign different users to different projects and assign different permissions to different users to manage and maintain different databases

1. Create user

In MySQL, all users are in the user table under the MySQL database, there are many fields in the user table, but the most important is the first three fields: Host, User, Password

Create user username [@ host address] identified by ' password ';

Create a user who can access the LAN
Create user ' user2 ' @ ' 192.168.148.% ' identified by ' 123456 ';

Create a user that can be accessed all over the network
Create user ' User3 ' identified by ' 123456 ';

2. Assigning Permissions to users
　　Grant permission list on database. data table to User (username @ host address);

Grant Select,update on php2016.php_student to ' user2 ' @ ' 192.168.148.% ';

3. Reclaim Permissions
Revoke update on php2016.php_student from ' user2 ' @ ' 192.168.148.% ';

4. Delete users
Drop user ' user1 ' @ ' localhost ';

Third, password management

1. Change the root password

There are generally two ways to modify:

1) through mysqladmin Modify

mysqladmin is also a client that is typically used to perform some administrative work, which can also be modified Root User's password

Mysqladmin-hpup Password New password

2) to log on to the server first, modify it directly using the following command:

　　Set Password = password (' new password ') //This method is also suitable for ordinary users

2, theroot user to modify the password of ordinary users

　　Set password for ' username ' @ ' host address ' = password (' new_password ');

3, root password back

1) Stop MySQL Service net stop MySQL

2) Enter MySQL Safe Mode, that is, you can enter the database without entering a password

　　　　Mysqld-nt--skip-grant-tables;

3) re-open a cmd Command Line window, enter mysql–uroot–p , log in with a blank password MySQL

4) enter the following command to start the modification Root User's password

　　　　Update Mysql.userset Password=password (' New_password ') where user= ' root ';

5) Refresh Permissions

　　　　Flush privileges;

6) exit: quit

7) End in Memory mysqld process!

8) Restart MySQL Server

back Root The password must be fast! Because in safe mode,mysql server is the most vulnerable, all people can not need password directly as root user login, and are super Administrator privileges!

Backup and recovery of data and user Rights management