1.linux under the command to start MySQL:
The code is as follows |
Copy Code |
Mysqladmin start /ect/init.d/mysql Start (Previous installation path for MySQL) |
2.linux Restart MySQL command:
The code is as follows |
Copy Code |
Mysqladmin restart /ect/init.d/mysql Restart (previous installation path for MySQL) |
3.linux to close MySQL command:
The code is as follows |
Copy Code |
Mysqladmin-u root-p Password shutdown /ect/init.d/mysql Shutdown (Previous installation path for MySQL) |
4. Connect MySQL on this computer:
Enter the directory Mysqlbin, then type the command mysql-uroot-p and prompt for the password when you enter.
Exit MySQL command: Exit (enter)
5. Modify MySQL Password:
The code is as follows |
Copy Code |
Mysqladmin-u username-p Old password password new password or enter the MySQL command line set PASSWORD for ' username ' @ ' host ' = PASSWORD (' password '); GRANT USAGE on *.* to ' username ' @ ' host ' identified by ' biscuit '; |
To modify your own password
The code is as follows |
Copy Code |
SET PASSWORD = PASSWORD (' biscuit '); |
6. Add new users. (Note: The commands in the MySQL environment are followed by a semicolon as the command Terminator)
The code is as follows |
Copy Code |
Grant all privileges in *.* to username @ '% ' identified by ' password ' with GRANT option; flush privileges; (Refresh permission settings) Grant SELECT on database. * To User name @ login host identified by "password" |
If you add a user test password of 123, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First connect the root user to MySQL, and then type the following command:
The code is as follows |
Copy Code |
Grant Select,insert,update,delete on *.* to "identified by" 123; |
7. Skip authorized access to MySQL
The code is as follows |
Copy Code |
Mysqld_safe--user=mysql--skip-grant-tables--skip-networking & |
Second, the MySQL database aspects of the operation
You must first log in to MySQL, the operation is done under the MySQL prompt, and each command ends with a semicolon
1. Display the list of databases.
The code is as follows |
Copy Code |
show databases; |
2, display the data table in the library:
The code is as follows |
Copy Code |
Use MySQL; Open Library Show tables; |
3, display the structure of the data table:
The code is as follows |
Copy Code |
describe table name; |
4, build the library:
Create database library name;
5, the establishment of the table:
The code is as follows |
Copy Code |
Use library name; CREATE TABLE table name (field set list); |
6, delete the database and delete the table:
drop Database library name;
The code is as follows |
Copy Code |
The drop table table name; |
7. Empty the record of the table:
The code is as follows |
Copy Code |
Delete from table name; |
8, display the records in the table:
The code is as follows |
Copy Code |
SELECT * from table name; |
9, the Code modification
If you want to change the entire MySQL encoding format:
When you start MySQL, the Mysqld_safe command line joins
The code is as follows |
Copy Code |
--default-character-set=gbk |
If you want to change the encoding format of a library: Enter a command after the MySQL prompt
The code is as follows |
Copy Code |
ALTER DATABASE db_name default character set GBK; |
Iii. Import and export of data
1, the text data to go to the database
The format that text data should conform to: field data is separated by a TAB key and Null values are used instead. Cases:
1 Name Duty 2006-11-23
Data incoming command load infile "filename" into table name;
2. Export Database and table
The code is as follows |
Copy Code |
Mysqldump--opt News > News.sql |
(Back up all tables in the database news to the News.sql file, News.sql is a text file, and the filename is optional.) )
The code is as follows |
Copy Code |
Mysqldump--opt News Author Article >author.article.sql ( |
Back up the author table and article table in the database news to the Author.article.sql file, Author.article.sql is a text file, and the file name is optional. )
The code is as follows |
Copy Code |
Mysqldump--databases db1 DB2 > News.sql |
(Back up the database Dbl and DB2 to the News.sql file, News.sql is a text file, and the file name is optional.) )
The code is as follows |
Copy Code |
Mysqldump-h host-u user-p pass--databases dbname > File.dump |
is to import the database dbname in the host with the name user, password pass to the file File.dump
The code is as follows |
Copy Code |
Mysqldump--all-databases > All-databases.sql (Backup all databases to All-databases.sql file, All-databases.sql is a text file, file name is optional.) ) |
3. Import data
The code is as follows |
Copy Code |
MySQL < all-databases.sql (Import Database) Mysql-u root-p Fukai–force < Dmc010003_db.myisam.sql (forced import) Mysql>source news.sql (execute under mysql command, import table) |
Introduction to Mysqlimport's common options:
-D or--delete all information in the data table before the new data is imported into the datasheet
-F or--force Mysqlimport will force continued insertion of data regardless of whether an error has been encountered
-I or--ignore mysqlimport skips or ignores rows with the same unique keyword, the data in the import file is ignored.
-L or-lock-tables Lock the table before the data is inserted, which prevents the user's queries and updates from being affected when you update the database.
-R or-replace This option is the opposite of the-I option; This option will replace the record for the same unique key in the representative.
--fields-enclosed-by= char Specifies what is included in the record of the data in the text file, in many cases the data is enclosed in double quotes. By default, data is not Fu Guachi by character.
--FIELDS-TERMINATED-BY=CHAR Specifies the separator between the values of each data, and the separator is a period in a file separated by a period. You can specify the separator between data by using this option.
The default delimiter is Hop Geff (TAB)
--lines-terminated-by=str This option to specify a delimited string or character of data between rows and lines in a text file. By default, Mysqlimport is newline as a row separator.
You can choose to replace a single character with a string:
A new line or a carriage return.
The Mysqlimport command commonly used options are the-V display (version),-p prompts for a password (password)
Example: Import a comma-delimited file
The record format for rows in a file is this:
The code is as follows |
Copy Code |
"1", "ORD89876", "1 dozen Roses", "19991226" |
Our task is to import the data from this file into the table orders in the database Meet_a_geek, we use this command:
The code is as follows |
Copy Code |
Bin/mysqlimport–prl–fields-enclosed-by= "–fields-terminated-by=, Meet_a_geek Orders.txt |
First, connect MySQL
Format: mysql-h host address-u user name-P user Password
1, Example 1: Connect to MySQL on this machine.
First in the Open DOS window, and then into the directory Mysqlbin, and then type the command mysql-uroot-p, enter after the prompt you to lose the password, if just installed MySQL, superuser root is no password, so direct return can enter into MySQL, The MySQL prompt is: mysql>.
2, Example 2: Connect to the remote host MySQL. Assume the IP of the remote host is: 110.110.110.110, username is root, password is abcd123. Type the following command:
The code is as follows |
Copy Code |
Mysql-h110.110.110.110-uroot-pabcd123 |
(Note: U and root can be without spaces, others are the same)
3, exit MySQL command: Exit (enter).
MySQL Common maintenance command
The code is as follows |
Copy Code |
1, show global status; list MySQL server running various status values 2, show variables; query MySQL server configuration information statement |
3, view the slow query
The code is as follows |
Copy Code |
Show variables like '%slow% '; Show global status like '%slow% '; |
4, the maximum number of connections
The code is as follows |
Copy Code |
Show variables like ' max_connections '; Maximum number of MySQL server connections Show global status like ' Max_used_connections '; Maximum number of connections to the server response |
5. View table structure
code is as follows |
copy code |
Desc TableName; Describe TableName; Show columns from TableName; Show CREATE TABLE tablename; |