MySQL Login and management operation under Linux

First, MySQL database start and stop
1.linux commands to start MySQL:
Mysqladmin start
/ect/init.d/mysql start (front of MySQL installation path)
2.linux Restart MySQL command:
Mysqladmin restart
/ect/init.d/mysql Restart (front of MySQL installation path)
3.linux the command to turn off MySQL:
Mysqladmin shutdown
/ect/init.d/mysql shutdown (front of MySQL installation path)
4. Connect to MySQL:
Format: mysql-h host address-u user name-P user Password
Connect the native MySQL, enter the directory Mysql\bin, and then type the command mysql-uroot-p, enter the password after the prompt.
Connect to MySQL on the remote host. Assume the remote host IP is: 110.110.110.110, the user name is root, the password is abcd123. Type the following command:
Mysql-h110.110.110.110-uroot-pabcd123

Or: mysql-h127.0.0.1-uroot-pabcd123

(Note: You and root can be used without spaces, others are the same)
Exit MySQL command: Exit (enter)
5. Modify the MySQL password:
Format: Mysqladmin-u username-P Old password password new password
or go to MySQL command line set PASSWORD for Root=password ("root");
such as: Mysqladmin-uroot-password newpwd
Note: Because Root does not have a password at the beginning, the-p old password can be omitted.
6. Add new users. (Note: Commands in the MySQL environment are followed by a semicolon as a command terminator)
Format: Grant Select on database. * To User name @ login host identified by "password"
If you add a user test1 password to 123, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First, use the root user to connect to MySQL, and then type the following command:
Grant Select,insert,update,delete on * * to [email protected]\ "%\" Identified by "123";
If you add a user test2 password to ABC, so that he can only log on on localhost, and can query, insert, modify, delete the database mydb (localhost refers to the local host, that is, the MySQL database is located in the host), This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, but only through a Web page on the MySQL host.
Grant Select,insert,update, delete on MyDB. * to [e-mail protected] identified by "ABC";
If you do not want to test2 have a password, you can call another command to erase the password.
Grant Select,insert,update,delete on MyDB. * to [e-mail protected] identified by "";
Second, MySQL database operation
You must first log in to MySQL (Mysql-u root-p), and the operation is performed at the prompt of MySQL, and each command ends with a semicolon;
1. Display the list of databases.
show databases;
2. Display the data table in the library:
use MySQL;//Open library MySQL
Show tables;
3, display the structure of the data table:
describe table name;
4, build the library:
Create database name;
5, build the table:
Use library name;
CREATE TABLE table name (field settings list);
6. Deleting the library and deleting the table:
drop database name;
drop table name;
7. Empty the records in the table:
Delete from table name;
8. Display the records in the table:
SELECT * from table name;
9, the revision of the Code
If you want to change the entireMySQLOfCodingFormat:
StartMySQL, the Mysqld_safe command line joins
--default-character-set=gbk

If you want to change a library'sCodingFormat: InMySQLPrompt after input command
ALTER DATABASE db_name default character set GBK;
iii. Import and export of data
1. The text data goes to the database
The format that text data should conform to: The field data is separated by the TAB key, and the null value is used instead. Cases:
1 Name Duty 2006-11-23
Data incoming command load data local infile "file name" into table name;
2. exporting databases and tables
Mysqldump--opt News > News.sql (back up all the tables in database news to the News.sql file, News.sql is a text file, and the file name is taken.) ）
Mysqldump--opt News Author article > Author.article.sql (back up author tables and article tables in database news to author.article.sql files, Author.article.sql is a text file, and the file name is either taken. ）
Mysqldump--databases db1 DB2 > News.sql (Database dbl and DB2 back to News.sql file, News.sql is a text file, filename is taken.) ）
Mysqldump-h host-u user-p pass--databases dbname > File.dump
is to import the name of the host on the user, password pass database dbname into the file file.dump
Mysqldump--all-databases > All-databases.sql (Back up all databases to the All-databases.sql file, All-databases.sql is a text file, and the file name is taken.) ）
3. Import data
MySQL < all-databases.sql (Import Database)

Mysql>source News.sql; (executes under MySQL command, can import tables)

MySQL Login and management operation under Linux