MySQL command set

MySQL command totals

Linux_mysql command

1. View database status and start stop
/ETC/INIT.D/MYSQLD status
/etc/init.d/mysqld start
/etc/init.d/mysqld stop

2, configure the user initial password 123456:
Mysqladmin-u Root-password 123456

3, change the root user password to abc123
Mysqladmin-u root-p123456 Password abc123

4, if you want to remove the password:
mysqladmin-u root-pabc123 Password ""

5,root Connection database with password and no password:
Mysql-u Root (-uroot)-P
Mysql

6, add user test1 password ABC, allow it to log on any host, and have query, insert, modify, delete permissions for all databases:
Format: Grant Select on database. * To User name @ login host identified by "password"
Grant Select,insert,update,delete on * * to [e-mail protected] "%" identified by "ABC";

7, use the Test1 account to log in from another host command:

MYSQL-H host name-U TEST1-PABC
Eg:mysql-h 10.239.48.109-u TEST1-PABC

8, add a user test2, so that it can only login on localhost, and can query the database mydb, insert, modify, delete the operation,
This way the user can access the database directly from the Internet, even with a password that knows Test2, and is accessible only through a Web page on the MySQL host.
Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by "ABC";
Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by ""; Set No password

9, display the list of databases:
show databases;
Use MySQL to open the library
Show tables;

10. Operation of the table
describle table name; Display the structure of the data table
Create database name;
drop database name;
CREATE TABLE table name (Field Settings list)
drop table name;
Delete from table name; Clear table record
SELECT * from table name; Show records in table
Insert into table name values (,,)

ALTER TABLE name add column < field name >< field Options >

Export Data:
Mysqldump–opt Test > Mysql.test//Export database test to mysql.test file, followed by a text file
Mysqldump-u root-p123456–databases dbname > Mysql.dbname//The database dbname is exported to the file mysql.dbname.

Import data:
Mysqlimport-u root-p123456 < Mysql.dbname
Import text data into the database:
The fields of the text data are separated by the TAB key
Use test
Load data local infile "file name" into table name;
Eg:load data local infile "d:/mysql.txt" into table mytable;
Import. sql File command
Use database
SOURCE D:/mysql.sql;

The command to restart the Network service is service network restart

Restart the MySQL service command/etc/rc.d/init.d/mysqld restart

Restart FTP/ETC/RC.D/INIT.D/PROFTPD restart

View Process Ps-ef|grep mysqld

Kill Process Kill-9 20038

Start apace:/usr/local/apache/bin/httpd-k start

Restart Apace:/usr/local/apache/bin/httpd-k restart

Restart APAHCE/ETC/RC.D/INIT.D/HTTPD restart

Close apace:/usr/local/apache/bin/httpd-k stop

How to view CPU and memory usage under Linux: Top

Execute at the command line: Free
Htop

How to start/stop/restart MySQL

First, the starting mode

1. Start with service: Service mysqld start

2. Start with mysqld script:/etc/inint.d/mysqld start

3. Use Safe_mysqld to start:safe_mysqld&

Second, stop

1. Start with service: Service mysqld stop

2. Start with mysqld script:/etc/inint.d/mysqld stop

3, mysqladmin shutdown

Third, restart

1. Start with service: Service mysqld restart

2. Start with mysqld script:/etc/inint.d/mysqld restart
Question Editor Summary

How to start/stop/restart MySQL
First, the starting mode

1. Start with service: Service mysqld start

2. Start with mysqld script:/etc/inint.d/mysqld start

3. Use Safe_mysqld to start:safe_mysqld&

Second, stop

1. Start with service: Service mysqld stop

2. Start with mysqld script:/etc/inint.d/mysqld stop

3, mysqladmin shutdown

Third, restart

1. Start with service: Service mysqld restart

2. Start with mysqld script:/etc/inint.d/mysqld restart

When I first started to learn MySQL, I brought it with Redhat. What's the start/rc.d/init.d/start
This is very simple, but later the more learning, the system comes with MySQL, some versions are too low, some with
The low version of MySQL that you want the Web service to install

Later I learned to install MySQL in tar, my MySQL installed in the/usr/local/mysql directory

Have encountered many problems. The most common are:
ERROR 2002:can ' t connect to local MySQL server through socket

'/tmp/mysql.sock ' (111)

Workaround:
[Email protected] mysql]#/usr/local/mysql/bin/mysqladmin-u root/
>-s/var/lib/mysql/mysql.sock password ' your.passwd '
or make a connection.
Ln-s/var/lib/mysql/mysql.sock/tmp
In fact, the hint can not find/tmp/mysql.sock sometimes not the/tmp directory does not have this file, is the boot
The order is wrong, I've met
Common several start-up methods, themselves also not remember very clearly, if you are sure TMP under has mysql.sock this
Files you might want to try a few other commands.
/usr/local/mysql/bin/mysql-u root-p
/usr/local/mysql/bin/mysqld–user=mysql&
/usr/local/mysql/bin/mysqld–user=root&
/usr/local/mysql/bin/mysqld_safe–user=root&
/usr/local/mysql/bin/mysqld_safe–user=mysql&
/usr/local/mysql/bin/safe_mysqld–uer=root& (note that safe_mysqld and Mysqld_safe are different,& that MySQL runs in the background) I'll get an error.
Stopping server from PID file
/usr/local/mysql/data/localhost.localdomain.pid
060304 11:46:21 Mysqld Ended
This is a permissions issue, my MySQL directory belongs to the root user, also belongs to the root group, switch to Mysqld_safe boot is no problem,
You just have to pay attention to these few mysql,safe_mysqld,mysqld_safe,mysqld,mysqladmin. Try More
Several times
In fact, sometimes MySQL has started normally, check whether MySQL starts the command
Ps-aux | grep mysqld
You'll see something like this
MySQL 6394 0.0 1.5 10528 992 PTS/3 S 16:16 0:00
/usr/local/mysql/
MySQL 6395 0.0 1.5 10528 992 PTS/3 S 16:16 0:00
/usr/local/mysql/
MySQL 6396 0.0 1.5 10528 992 PTS/3 S 16:16 0:00
/usr/local/mysql/
Root 6422 0.0 1.1 2408 732 PTS/3 S 16:20 0:00 grep
Mysql

See if MySQL is listening on the port command
Netstat-tl | grep MySQL
You'll see something like this
TCP 0 0 *:mysql *:* LISTEN

From a lot of blogs.

MySQL command set