Linux mysql command Daquan

1.linux start mysql command: mysqladmin start /ect /init.d /mysql start (previously mysql installation path)

2.linux restart mysql command: mysqladmin restart /ect /init.d/mysql restart (in front of the mysql installation path)

3. Linux shutdown mysql command: mysqladmin shutdown /ect /init.d /mysql shutdown (previously mysql installation path) < Br>

4. Connect mysql on this machine: Enter the directory mysql\\bin, then type the command mysql -uroot -p, and press Enter to prompt for the password. Exit mysql command: exit (Enter)

5. Modify mysql password: mysqladmin -u username -p old password password new password or enter mysql command line SET PASSWORD FOR root=PASSWORD("root") ;

6. Add new users. (Note: the command in the mysql environment is followed by a semicolon as the command terminator.) grant select on database.* to username@login host identified by "password"such as adding a user test password to 123, so that he can Log in on any host and have the right to query, insert, modify, and delete all databases. First connect to mysql as the root user, then type the following command: grant select, insert, update, delete on *.* to " Identified by "123";

Second, about mysql database operations You must first log in to mysql, the operation is at the mysql prompt, and each command ends with a semicolon

1, display the database list. Show databases; 2, display the data table in the library: use mysql; //open the library show tables; 3, display the structure of the data table: describe table name; 4, build database: create database library name; 5, build table: use Library name; create table table name (field setting list); 6, delete library and delete table: drop database library name; drop table table name; 7, the table records empty: delete from table name; 8, display table Record: select * from table name;

9, encoding changes if you want to change the entire mysql encoding format: When starting mysql, mysqld_safe command line added --default-character-set=gbk

If you want to change the encoding format of a library: Enter the command alter database db_name default character set gbk at the mysql prompt;

Three, data import and export

1, text data Go to the format in which the text data in the database should match: the field data is separated by tabs, and the null value is used instead. Example: 1 name duty 2006-11-23 data incoming command load data local infile "file name" into table table name;

2, export database and table mysqldump --opt news > news. Sql (back up all the tables in the database news to the news.sql file, news.sql is a text file, the file name is arbitrary.) mysqldump --opt news author article > author.article.sql (will be in the database news The author table and the article table are backed up to the author.article.sql file, author.article.sql is a text file, and the file name is arbitrary.) mysqldump --databases db1 db2 > news.sql (back up the database dbl and db2 to news .sql file, news.sql is a text file, the file name is arbitrary.) mysqldump -h host -u user -p pass --databases dbname > file.dump is the host on the name user, password pass database Dbname is imported 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, the file name is arbitrary.)

3, import data mysql < al L-databases.sql (import database) mysql>source news.sql; (executed under the mysql command, you can import the table)

First, connect MySQL format: mysql -h host address -uusername-puser password

1, Example 1: Connect to MYSQL on this machine.

First open the DOS window, then enter the directory mysqlbin, then type the command mysql -uroot -p, after the carriage return prompts you to lose the password, if you just installed MYSQL, the super user root has no password, so directly Enter to enter MYSQL, MYSQL prompt is: mysql>.

2, Example 2: Connect to MYSQL on the remote host. Assume that the IP of the remote host is 110.110.110.110, the username is root, and the password is abcd123. Then type the following command:

mysql -h110.110.110.110 -uroot -pabcd123

(Note: u and root can be used without spaces, others are the same)

3 Exit the MYSQL command: exit (Enter).

Second, change password

Format: mysqladmin -u username -p old password password new password

1, example 1: add a password ab12 to root. First enter the directory mysqlbin under DOS, and then type the following command:

mysqladmin -uroot -password ab12

Note: Because root does not have a password at the beginning, the -p old password can be omitted. It is.

2, Example 2: Change the password of root to djg345.

mysqladmin -uroot -pab12 password djg345

Three, add new users. (Note: Unlike the above, the following is a command in the MySQL environment, so there is a semicolon as the command terminator)