Restore a database: mysql-h localhost-u root-p123456 www
Backing up a database: mysqldump-h localhost-u root-p123456 www > d:\www2008-2-26.sql
Where www is the database name
The following is a test in the program
$command = "Mysqldump–opt-h $dbhost-u $dbuser-P $dbpass $dbname | gzip > $backupFile ";
$command = "Mysqldump-h localhost-u root-p123456 guestbook > Guestbook2-29.sql";
System ($command);
echo "Success";
************************************************
Commands to back up the MySQL database
Mysqldump-hhostname-uusername-ppassword databasename > Backupfile.sql
Backing up the MySQL database to a format with a delete table
Backing up the MySQL database is a format with a delete table that allows the backup to overwrite the existing database without having to manually delete the existing database.
Mysqldump-–add-drop-table-uusername-ppassword databasename > Backupfile.sql
Compress the MySQL database directly backup
Mysqldump-hhostname-uusername-ppassword DatabaseName | gzip > backupfile.sql.gz
Back up a MySQL database (some) table
Mysqldump-hhostname-uusername-ppassword databasename specific_table1 specific_table2 > Backupfile.sql
Back up multiple MySQL databases at the same time
Mysqldump-hhostname-uusername-ppassword–databases databasename1 databasename2 databasename3 > Multibackupfile.sql
Backing up the database structure only
Mysqldump–no-data–databases databasename1 databasename2 databasename3 > Structurebackupfile.sql
Back up all databases on the server
Mysqldump–all-databases > Allbackupfile.sql
Commands to restore MySQL database
Mysql-hhostname-uusername-ppassword DatabaseName < Backupfile.sql
Restore a compacted MySQL database
Gunzip < backupfile.sql.gz | Mysql-uusername-ppassword DatabaseName
To transfer a database to a new server
Mysqldump-uusername-ppassword DatabaseName | Mysql–host=*.*.*.*-C DatabaseName
Several common use cases:
1. Export the entire database
Mysqldump-u user name-p database name > exported file name
Mysqldump-u root-p Dataname >dataname.sql
This time will prompt you to enter the root username password, enter the password after the Dataname database is successfully backed up in the mysql/bin/directory.
2. Export a table
Mysqldump-u user name-P database name Table name > exported file name
Mysqldump-u root-p dataname users> dataname_users.sql
3. Export a database structure
Mysqldump-u wcnc-p-d–add-drop-table SMGP_APPS_WCNC >d:\wcnc_db.sql
-D No data –add-drop-table add a drop table before each CREATE statement
4. Import the database
Common source Commands
Go to MySQL Database console,
such as Mysql-u root-p
Mysql>use Database
Then use the source command, followed by the script file (for example, the. SQL used here)
Mysql>source D:\wcnc_db.sql
Mysqldump supports the following options:
–add-locks
Add lock tables before each table is exported and then unlock table. (To make it faster to insert into MySQL).
–add-drop-table
Add a drop table before each create statement.
–allow-keywords
Allows you to create column names that are keywords. This is done by prefixing the table name with each column name.
-c,–complete-insert
Use the full INSERT statement (with the column name).
-c,–compress
If both the client and the server support compression, compress all the information between the two.
–delayed
Insert a row with the insert delayed command.
-e,–extended-insert
Use the new multi-line insert syntax. (Give a more condensed and faster INSERT statement)
-#,–debug[=option_string]
The use of the tracker (for debugging purposes).
Help
Displays a help message and exits.
–fields-terminated-by= ...
–fields-enclosed-by= ...
–fields-optionally-enclosed-by= ...
–fields-escaped-by= ...
–fields-terminated-by= ...
These options are used with the-T selection and have the same meaning as the corresponding load DATA infile clause.
LOAD DATA infile syntax.
-f,–flush-logs
Before starting the export, wash down the log files in the MySQL server.
-f,–force,
Even if we get a SQL error during the export of a table, continue.
-h,–host=.
Export data from a MySQL server on a named host. The default host is localhost.
-l,–lock-tables.
Locks all tables for starting export.
-t,–no-create-info
Do not write table creation information (CREATE TABLE statement)
-d,–no-data
No row information is written to the table. This is useful if you only want to export the structure of a table!
–opt
With –quick–add-drop-table–add-locks–extended-insert–lock-tables.
Should give you the fastest possible export for reading into a MySQL server.
-pyour_pass,–password[=your_pass]
The password to use when connecting to the server. If you do not specify the "=your_pass" section, Mysqldump requires a password from the terminal.
-P Port_num,–port=port_num
The TCP/IP port number to use when connecting to a single host. (This is used to connect to a host other than localhost because it uses a UNIX socket.) ）
-q,–quick
Do not buffer queries, export directly to stdout; use Mysql_use_result () to do it.
-s/path/to/socket,–socket=/path/to/socket
A socket file that is used with localhost when it is connected (it is the default host).
-t,–tab=path-to-some-directory
For each given table, create a Table_name.sql file that contains the SQL create command, and a table_name.txt file that contains the data. Note: This only works when the mysqldump is running on the same machine as the mysqld daemon. The format of the. txt file is determined by the –fields-xxx and –lines–xxx options.
-U user_name,–user=user_name
The user name that MySQL uses when connecting to the server. The default value is your UNIX login name.
-O var=option,–set-variable var=option
Sets the value of a variable. The possible variables are listed below.
-v,–verbose
Verbose mode. Print out more information that the program has done.
-v,–version
Print the version information and exit.
-w,–where= ' Where-condition '
Only the selected records are exported; Note that the quotation marks are mandatory!
"–where=user= ' JIMF '" "-wuserid>1″"-wuserid<1″
The most common use of mysqldump may be to make a backup of the entire database:
Mysqldump–opt Database > Backup-file.sql
But it is also useful to enrich another MySQL database with information from one database:
Mysqldump–opt Database | Mysql–host=remote-host-c Database
Since mysqldump exports a complete SQL statement, it is easy to import the data using the MySQL client program:
Shell> mysqladmin Create Target_db_name
shell> MySQL Target_db_name < backup-file.sql
is shell> MySQL Library name < file name
MySQL Rights Management
MySQL can give you a user grant one or more of the permissions, such as Select,insert,update,delete, mainly using the grant command, the usage format is:
Grant permissions on database object to user
A grant general data user, the right to query, insert, UPDATE, delete all table data in the database.
Grant Select on testdb.* to [email protected] '% '
Grant insert on testdb.* to [email protected] '% '
Grant update on testdb.* to [email protected] '% '
Grant Delete on testdb.* to [email protected] '% '
Alternatively, replace it with a MySQL command:
Grant SELECT, INSERT, UPDATE, delete on testdb.* to [email protected] '% '
II. Grant Database Developer, creating tables, indexes, views, stored procedures, functions ... and other permissions.
Grant creates, modifies, and deletes MySQL data table structure permissions.
Grant create on testdb.* to [email protected] ' 192.168.0.% ';
Grant ALTER on testdb.* to [email protected] ' 192.168.0.% ';
Grant drop on testdb.* to [email protected] ' 192.168.0.% ';
Grant operates MySQL foreign key permissions.
Grant references on testdb.* to [email protected] ' 192.168.0.% ';
Grant operates MySQL temp table permissions.
Grant create temporary tables on testdb.* to [email protected] ' 192.168.0.% ';
Grant operates MySQL index permissions.
Grant index on testdb.* to [email protected] ' 192.168.0.% ';
Grant operates the MySQL view, viewing the view source code permissions.
Grant CREATE view on testdb.* to [email protected] ' 192.168.0.% ';
Grant Show view on testdb.* to [email protected] ' 192.168.0.% ';
Grant operates MySQL stored procedures, function permissions.
Grant create routine on testdb.* to [email protected] ' 192.168.0.% '; -Now, can show procedure status
Grant alter routine on TESTDB.* to [email protected] ' 192.168.0.% '; -Now, can drop a procedure
Grant execute on testdb.* to [email protected] ' 192.168.0.% ';
Grant General DBA manages permissions for a MySQL database.
Grant all privileges on TestDB to [email protected] ' localhost '
Where the keyword "privileges" can be omitted.
Grant Advanced DBA manages permissions for all databases in MySQL.
Grant all on * * to [email protected] ' localhost '
MySQL grant permissions can be used on multiple levels, respectively.
1. Grant acts on the entire MySQL server:
Grant SELECT On *. * to [email protected]; -DBAs can query the tables in all databases in MySQL.
Grant all on * * to [email protected]; -DBAs can manage all databases in MySQL
2. Grant acts on a single database:
Grant Select on testdb.* to [email protected]; -DBAs can query the tables in TestDB.
3. Grant acts on a single data table:
Grant SELECT, INSERT, UPDATE, delete on testdb.orders to [email protected];
4. Grant acts on the columns in the table:
Grant Select (ID, SE, rank) on testdb.apache_log to [email protected];
5. Grant acts on stored procedures, functions:
Grant execute on procedure testdb.pr_add to ' dba ' @ ' localhost '
Grant execute on function testdb.fn_add to ' dba ' @ ' localhost '
Vi. viewing MySQL user rights
To view the current user (own) permissions:
Show grants;
To view additional MySQL user rights:
Show grants for [email protected];
Vii. revoke permissions that have been given to MySQL users.
Revoke is similar to Grant's syntax, just replace the keyword "to" with "from":
Grant all on * * to [email protected];
Revoke all on * * from [email protected];
Viii. MySQL Grant, revoke user rights considerations
1. Grant, after revoke user rights, the user has to reconnect to the MySQL database for the permission to take effect.
2. If you want to allow authorized users, you can also grant these permissions to other users, you need the option "grant option"
Grant Select on testdb.* to [e-mail protected] with GRANT option;
This feature is generally not available. In practice, database permissions are best managed centrally by DBAs.
Note: Be sure to refresh the service after modifying the permissions, or restart the service, refresh the service by: Flush privileges.
View User Permissions
Show grants for your users
Like what:
Show grants for [email protected] ' localhost ';
MySQL database backup and restore command
