Start
Mysqld
Login
Mysql-u username-P Password
Stop
Mysqladmin-u username Shutdown
View Current Character Set settings
Show variables like 'character/_ set/_ % ';
Set Character Set
Set character_set_results = GBK;
Set the character set of client, connection, and results at one time
Set names 'ust8'
Set names should be set to the character set of the command line, instead of the database
View the table's common script Structure
Show create table table_name;
View table structure
Desc table_name
Modify the character set of a table
Alter table tablename convert to Character Set utf8;
Backup and recovery database
1. Export the entire database
Mysqldump-u username-P Database Name> exported file name
Mysqldump-u wcnc-p -- dufalut-character-set = GBK smgp_rj_wcnc> wcnc. SQL
It is best to add character sets when exporting, especially when exporting using command lines.
2. Export a table
Mysqldump-u user name-P database name Table Name> exported file name
Mysqldump-u wcnc-P smgp_rj_wcnc users> wcnc_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 adds a drop table before each create statement.
4. Export stored procedures and functions
Mysqldump-u wcnc-p-r smgp_rj_wcnc> wcnc. SQL
-R: Export stored procedures and functions
5. Import the database
Common source commands
Go to the MySQL Database Console,
For example, MySQL-u root-P
Mysql> Use Database
Then run the source command. The following parameter is the script file (for example,. SQL used here)
Mysql> source D: wcnc_db. SQL
How to modify MySQL Character Set
Modifying the default Character Set of MySQL is implemented by modifying its configuration file. Generally, there are two situations:
Windows Platform
The MySQL configuration file in Windows is my. ini, which is usually in C:/Windows/My. ini or C:/winnt/My. ini.
Default-character-set = GBK # Or gb2312, big5, utf8
Then restart MySQL
Service MySQL restart
Or
/Etc/init. d/MySQL restart
Or use another method to restart the instance.
UNIX platform
The MySQL configuration file in Linux is my. CNF, which is usually/etc/My. CNF. If you cannot find it, run the find command to find it:
Find/-INAME my. CNF add
Default-character-set = GBK # Or gb2312, big5, utf8
Then restart MySQL
Net stop MySQL
Net start MySQL
It takes effect.
Table engine:
The default MySQL table engine is MyISAM, which does not support transactions and only stores data. It has the advantage of high speed.
InnoDB is not mysql fast, but it supports transactions.
Create a user to assign Permissions
Add: mysql> grant all on DB. *
'Username' @ localhost (IP) identified by 'Password ';
Refresh the permission table mysql> flush privileges;
Delete: mysql> use MySQL;
Mysql> Delete from user where username = 'username ';
Modify: mysql> use MySQL;
Mysql> Update user set username = 'username', password = PASSWORD ('Password ');...
After modification, execute the command;
Flush privileges;
Refresh permission
Configure MYSQL:
1. Find a configuration file of huge. ini in the folder, open it, and add two rows in the [mysqld] field: basedir = C:/mysql6
Datadir = C:/mysql6/Data
Save it as my. ini file and put it in the C:/Windows directory.
2. open cmd, enter the bin directory of the folder, that is, C:/mysql6/bin, enter mysqld-NT-install, and load the MySQL service. The system will prompt that the installation is successful, enter Net start MySQL to start the MySQL service.
3. Enter mysql-uroot-P in the bin directory and press enter to enter the password. If the initial password is empty, press enter to enter the MySQL welcome page.
The initial configuration has ended.
If you want to exit the service, first enter net stop MySQL on the terminal, and then enter mysqld-NT-Remove in the bin directory to completely terminate the MySQL service.
The new version of MySQL to load Windows Services is:
Mysqld -- install MySQL -- defaults-file = D:/MySQL/My. ini
MySQL is the service name
Uninstall Service
Mysqld-Remove
Generate UUID
Select UUID () from tablename;
View connections
Show processlist;
View database Parameters
Show variables;
Define the block so that the ";" error will not be reported when writing the stored procedure in the command line.
Delimiter //
......
//
Delete table data without transactions
Truncate table table_name