Mysql commands used in Linux
1.linux start the mysql command:
/ Ect / init.d / mysql start (in front of the mysql installation path)
2.linux to restart the mysql command:
/ Ect / init.d / mysql restart (in front of the mysql installation path)
3.linux shut down the mysql command:
mysqladmin-u root-p password shutdown
/ Ect / init.d / mysql shutdown (in front of the mysql installation path)
4 Connect the machine to mysql:
Into the directory mysql \ bin, then type the command mysql-uroot-p, enter after the password.
Exit mysql command: exit (Enter)
5. Change mysql password:
mysqladmin-u username-p password old 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';
Modify their own password SET PASSWORD = PASSWORD ('biscuit');
6 to add new users. (Note: mysql environment after the command with a semicolon as a command terminator)
grant all privileges on *.* to username @ '%' identified by 'password' with grant option;
flush privileges; (refresh permissions)
grant select on database .* to username @ log host identified by "password"
Such as adding a user test password is 123, so that he can log on any host, and all databases have query, insert, update, delete permissions. First, the root user to connect into the mysql, and then type the following command:
grant select, insert, update, delete on *.* to "Identified by" 123 ";
7. Skip authorized to access mysql
mysqld_safe - user = mysql - skip-grant-tables - skip-networking &
Second, the mysql database in operation must first log into the mysql, the operations are carried out in the mysql prompt, and end each command with a semicolon
1, shows the list of databases.
2 shows the data in the database table:
use mysql; / / open the library
3 shows the structure of the data sheet:
4, building a database:
create database database name;
5, the construction of the table:
use library name;
create table table name (field set list);
6, delete the library and delete tables:
drop database database name;
drop table table name;
7, recorded in the table empty:
delete from table name;
8, show records in the table:
select * from table name;
9, code changes if you want to change the mysql encoding format:
Mysql start time, mysqld_safe command line to
- Default-character-set = gbk
If you want to change the encoding format of a database: the mysql prompt, enter the command
alter database db_name default character set gbk;
Third, data import and export
1, the Chinese text data to the database format of the data should be consistent: use the tab key between fields separated data, null values are used instead. Example:
1 name duty 2006-11-23
Data into the command load data local infile "filename" into table table name;
2, export the database and tables
mysqldump - opt news> news.sql (the news all the tables in the database backup to news.sql file, news.sql is a text file, the file name either to take.)
mysqldump - opt news author article> author.article.sql (database news article in the author table and the table back to author.article.sql file, author.article.sql is a text file, the file name either to take.)
mysqldump - databases db1 db2> news.sql (dbl and db2 database backup to news.sql file, news.sql is a text file, the file name either to take.)
mysqldump-h host-u user-p pass - databases dbname> file.dump
Is the host name of the user, the password of the database dbname will pass into the file file.dump
mysqldump - all-databases> all-databases.sql (all to the all-databases.sql database backup file, all-databases.sql is a text file, the file name either to take.)
3, import data
mysql <all-databases.sql (into database)
mysql-u root-p fukai-force <dmc010003_db.myisam.sql (forced into)
mysql> source news.sql; (in the mysql command executed, the table can be imported)
MySQLimport common option description:
-D or - delete the new data into the data table to delete the data before all of the information in the data table
-F or - force regardless of whether an error is encountered, MySQLimport forces continue to insert data
-I or - ignore MySQLimport skip or ignore the only keyword that has the same line, the data in the import file will be ignored.
-L or-lock-tables locks tables before the data is inserted, thus prevented, you update the database, the user's query and update affected.
-R or-replace this option with the-i option does the opposite; for this option will have the same unique key on behalf of the record.
- Fields-enclosed-by = char data in the specified text file to record what is enclosed, and in many cases the data in double quotes. By default data is not enclosed in the character.
- Fields-terminated-by = char all the data specified separator between the values in the period-separated file, the separator is a period. You can use this option to specify the separator between data.
The default delimiter is a tab character (Tab)
- Lines-terminated-by = str This option specifies the text file data between the rows and rows separated by a string or character. By default MySQLimport the newline as a line separator.
You can choose to use a string to replace a single character:
A new line or a carriage return.
MySQLimport commands commonly used options as well-v show version (version),-p prompts for password (password)
Example: Importing a comma delimited file
Lines in a file record format is this:
"1", "ORD89876", "1 Dozen Roses", "19991226"
Our task is to put this file inside the data into a database Meet_A_Geek the table Orders, we use this command:
bin / MySQLimport-prl-fields-enclosed-by = "-fields-terminated-by =, Meet_A_Geek Orders.txt
First, connect to MySQL
Format: mysql-h host address-u username-p password
1, Example 1: Connect to the machine on MYSQL.
First, open a DOS window, then enter the directory mysqlbin, and then type the command mysql-uroot-p, enter the password after the prompt you lose, If you have just installed MYSQL, there is no super-user root password, so you can directly enter into the in the MYSQL, MYSQL prompt is: mysql>.
2, Example 2: connect to remote host on MYSQL. Assuming the remote host's IP is: 18.104.22.168, user name is root, password is abcd123. Type the following command:
(Note: u and the root can not add space, the other is the same)
3, exit MYSQL command: exit (Enter).
mysql commands used to maintain
1, show global status; The MySQL server is running all kinds of state values
2, show variables; query MySQL server configuration information statement
3, see slow queries
show variables like '% slow%';
show global status like '% slow%';
4, the maximum number of connections
show variables like 'max_connections'; MySQL server maximum number of connections
show global status like 'Max_used_connections'; the maximum number of connections the server response
5, see table structure
show columns from Tablename;
show create table Tablename;
ALTER TABLE - change the table properties
ALTER TABLE - change the table attribute fields:
alter table `user_movement_log`
Add column GatewayId int not null default 0 AFTER `Regionid` (in which fields to append)
alter table `user_movement_log` drop column Gatewayid
Adjust the field order:
ALTER TABLE `user_movement_log` CHANGE `GatewayId` `GatewayId` int not null default 0 AFTER RegionID