Common MySQL operation and maintenance commands

Common MySQL operation and maintenance commands

1. Change Password:

Mysqladmin-u root-p password New password

Enter the old password.

2. Export the entire database:

Mysqldump-u username-p Database Name> exported file name

3. Import the database:

Go to the mysql Database Console,

For example, mysql-u root-p

Mysql> use Database Name

Mysql> source d: test. SQL

4. Restore the database:

A. repair a single database:

Mysqlcheck-r-o-u root database name-p Password

For example, mysqlcheck-r-o-u root sq_test-p (root password)

B. Fix all databases:

Mysqlcheck-A-r-o-u root-p

Enter the root password.

C. Fixed the table in a database:

Myisamchk-r-q database path (-r-q means "quick recovery mode ").

For example, myisamchk-r-q/data/sq_test/*. MYI

5. Create a database and its users

Mysql4

Create DataBase name;

GRANT Select, Insert, Update, Delete, Create, Drop, INDEX, Alter ON database name. * to login name @ "%" identified by "password ";

Mysql5

Create DataBase name;

GRANT Create VIEW, show view, Create ROUTINE, Alter ROUTINE, EXECUTE, Select, Insert, Update, Delete, Create, Drop, INDEX, Alter ON Database Name. * to login name @ "%" identified by "password ";

6. Innodb database conversion to MyISAM Database

A. Back up innodb using mysqldump as root.

B. Use EmEditor to open the backup file. (Some codes may not be identified, resulting in loss of encoding. A syntax error will be reported when the import is completed. It is best to use UE.

Use the search-replace function to replace ENGINE = InnoDB with TYPE = MyISAM to save the file.

C. Back up the original file directories of the innodb engine to other places, and delete all the tables in the directory so that they can be restored after failure.

D. re-import the database.

7. MYSQL database Restoration:

A. if the customer is using the DISCUZ or PHPWIND Forum program, regardless of the database version, MYSQL4.X or MYSQL5.X, they can use the database restoration tool provided by the Forum to restore it, you do not need to consider database migration. Remember that this can save a lot of time and effort.

B. mysqldump-u username -- compatible = mysql40-p Database Name> exported file name

C. If the MYSQL database fails to be imported, modify the database file ENGINE = MyISAM default charset = gbk to TYPE = MyISAM and then import it.