MySQL Common Operations Command

Mysql forgot root password

Method One:
Add below/etc/my.cfg [mysqld]
Skip-grant-tables or Skip-grant.

Restart MySQL
Use MySQL;
UPDATE user SET Password = Password (' New-password ') WHERE user = ' root ';
MySQL flush privileges;
Change the My.cfg file back and restart MySQL.

Method Two:

Add parameter--skip-grant-tables When starting MySQL server to skip authentication of authorization table
(./safe_mysqld--skip-grant-tables &) so that we can directly log on to the MySQL server,
Then change the root user's password and restart MySQL to log in with the new password.

Adding environment variables
Export Path=/usr/local/mysql/bin: $PATH add to/etc/profile.d/mysql.sh


Simply use the MySQL service on your computer
You can also add the--skip-networking parameter at startup to keep MySQL from listening to any TCP/IP connection
(./safe_mysqld--skip-networking &) for added security.

View supported storage engines

mysql> show engines;+--------------------+---------+--------------------------------------------- -------------------+--------------+------+------------+| engine              | Support | Comment                                                           | transactions |  xa   | savepoints |+--------------------+---------+---------------------------- ------------------------------------+--------------+------+------------+| myisam              | yes     | myisam  storage engine                                            | NO            | no   | no         | |  csv                |  YES     | CSV storage engine                                                | NO           | NO    | no          | |  MRG_MYISAM         | YES      | collection of identical myisam tables                            | no           | no   |  no         | |  BLACKHOLE          | YES      | /dev/null storage engine  (anything you write to it  Disappears)  | NO           | NO    | no         | |  memory         &Nbsp;   | yes     | hash based, stored in  memory, useful for temporary tables      | no            | NO   | NO          | |  FEDERATED          | NO       | Federated MySQL storage engine                                   | NULL          | null | null       | |  ARCHIVE            | YES      | archive storage engine                                           | NO            | NO   | NO          | |  PERFORMANCE_SCHEMA | YES     | Performance Schema                                                | NO            | no   | no         | |  innodb             | default |  supports transactions, row-level locking, and foreign keys      | yes          | yes  |  yes        |+--------------------+---------+-------------------- --------------------------------------------+--------------+------+------------+9 rows in set   (0.01 SEC)

View the current MySQL default data engine

Mysql> Show variables like '%engine% '; +---------------------------+--------+| variable_name | Value |+---------------------------+--------+| Default_storage_engine | InnoDB | | Engine_condition_pushdown | On | | Storage_engine |   InnoDB |+---------------------------+--------+3 rows in Set (0.00 sec) mysql> Show variables; View related parameters

View and modify the maximum number of connections

A site that uses a MySQL database, "Too many connections" error occurs when there are too many access connections.
There are two situations where this error occurs:

One is that the site traffic is too large, the server can not afford, at this time should consider load balancing or other ways to reduce server pressure.

Another situation is that MySQL's maximum number of connections is set too small, and when the traffic is slightly larger, there are too many connections.

Show variables like '%max_connections% ';
To modify the maximum number of MySQL connections, simply modify the max_connections value in the MY.CNF configuration file and restart MySQL.

If the max_connections entry is not found in the My.cnf file, you can add the following entry yourself.
Max_connections = 220
Or
Mysql>set Global max_user_connections = 200; [Valid only for this modification]

Real-time view of MySQL current connections
Prerequisite: Authorized for user and remote host

Mysql> Grant All on * * to [e-mail protected] identified by ' 123456 ';
mysql> flush Privileges;

[Email protected] ~]# mysqladmin-uroot-p-h192.168.9.9 processlist;

View only the current number of connections (threads is the number of connections)
[[email protected] ~]# mysqladmin-uroot-p123456 status;
uptime:4449 threads:2 questions:18697 Slow queries:0 opens:77 Flush tables:1 Open tables:31 queries per Seco nd avg:4.202

Mysql> Show full processlist;

Or

Mysql> Show status;

Take a look at all the connection processes and see if the process waits and whether the state is locked
If there are too many processes, print the process down and look at it.
Mysql-e ' show full processlist; '-p > 111.txt
Finding non-locked processes is generally the reason for the current execution of the card, which causes the subsequent process to queue.

View MySQL connections and current user MySQL connection count
Enter the MySQL prompt with the administrator first.
Mysql-uroot-pxxxx
MySQL >show processlist; #可以显示前100条连接信息
MySQL >show full processlist; #可以显示全部. If you log in with a regular account, only the user is shown.

Modifying the MySQL listening port
1. If the system comes with a RPM package:
Modify/ETC/MY.CNF
[Mysqld]
Datadir=/var/lib/mysql
port=3300 #修改成你自己想要的端口
Socket=/var/lib/mysql/mysql.sock
[Mysql.server]
User=mysql
Basedir=/var/lib
[Safe_mysqld]
Err-log=/var/log/mysqld.log
Pid-file=/var/run/mysqld/mysqld.pid

2. If you are installing a source package or installing multiple MYSQLD services on the same server:
Locate the my.cnf file. Add port= port number
It is important to note that
[Client]
Port = 3300
We have to change it here.

Configure two MySQL services on the same machine (run two ports)
cd/usr/local/;

Cp-r MySQL mysql_2

CD mysql_2;
Initialize MYSQL2

./scripts/mysql_install_db--user=mysql--DATADIR=/DATA/MYSQL2
Copy the configuration file:

Cp/etc/my.cnf./my.cnf
Modify profile related parameters: Vim my.cnf #更改port and socket
Start:/usr/local/mysql_2/bin/mysqld_safe--defaults-file=/usr/local/mysql_2/my.cnf--user=mysql &
If you boot it, you need to add it to the/etc/rc.local.

View MySQL server current version, current date, current user

Mysql> select version ();
Mysql> select Current_date ();

Mysql> Select User ();

View MySQL Status

Mysql> Show status;

modifying MySQL parameters
Mysql> Show variables like ' max_connect% ';

mysql> Set Global max_connect_errors = 1000;

Turn on the server　
/etc/rc.d/init.d/mysqld start

Or
Mysqld_safe--user=mysql &

Stopping the server
/etc/rc.d/init.d/mysqld stop
Or
/usr/bin/mysqladmin-u root-p shutdown

Change MySQL directory
The default data file storage directory for MySQL is/var/lib/mysql. The following steps are required if you want to move the directory to/home/data:
1. Set up the data directory in the home directory
Mkdir/home/data
2. Stop the MySQL service process:
Mysqladmin-u root-p shutdown
3. Move/var/lib/mysql Entire directory to/home/data
mv/var/lib/mysql/home/data/
This will move the MySQL data file to/home/data/mysql.
4. Locate the MY.CNF configuration file
If the/etc/directory does not have a MY.CNF profile, locate the *.cnf file under/usr/share/mysql/, copy one of them to/etc/and rename it to MY.CNF. The command is as follows:
[Email protected] mysql]# CP/USR/SHARE/MYSQL/MY-MEDIUM.CNF/ETC/MY.CNF
5. Edit the MySQL configuration file/etc/my.cnf
To ensure that MySQL works correctly, you need to indicate where the Mysql.sock file is generated. Modify the value in the Socket=/var/lib/mysql/mysql.sock line to the right of the equals sign:/home/mysql/mysql.sock.
The operation is as follows:
Vim/etc/my.cnf
# The MySQL server
[Mysqld]
Port = 3306
#socket =/var/lib/mysql/mysql.sock (original content, for more secure use # Note this line)
Socket =/home/data/mysql/mysql.sock (plus this line)
6. Modify MySQL startup script/etc/rc.d/init.d/mysql
Modify the MySQL startup script/etc/rc.d/init.d/mysql, and change the path to the right of the equal sign in the Datadir=/var/lib/mysql line to your current actual storage path:/home/data/mysql.
[Email protected] etc]# Vi/etc/rc.d/init.d/mysql
#datadir =/var/lib/mysql (Note this line)
Datadir=/home/data/mysql (plus this line)
7. Restart MySQL Service
/etc/rc.d/init.d/mysql start
If the work is moving properly, otherwise check the previous 7 steps.

Empowering
MYAQL >grant all on dbname.* to [e-mail protected]% identified by ' password ';
MySQL >flush privileges; Refresh Permissions table

Optimizing tables

mysql> use Qqtexas;
mysql> optimize table users;

mysql> optimize table Users,userid;

or optimize and check for repair tasks

[Email protected] ~]# mysqlcheck-o dbname tbla TblB tblc-u root-p

To analyze a table　
mysql> use Qqtexas;

mysql> Analyze table Tbla;
Or
[Email protected] ~]# mysqlcheck-a dbname [Tbla tblB TBLC]-u root-p

Check the table

mysql> use Qqtexas;

mysql> Check table users;

Log on to the MySQL database server from the command line

1. Log in to MySQL using the default 3306 port
   /usr/local/mysql/bin/mysql-u root-p

2. Manage multiple MySQL on different ports over a TCP connection (note: This feature is only available with MySQL4.1 or later)
   /usr/local/mysql/bin/mysql-u root-p--protocol=tcp--host=localhost--port=3307

3. Manage multiple MySQL on different ports via socket sockets
   /usr/local/mysql/bin/mysql-u root-p--socket=/tmp/mysql3307.sock

4. Manage multiple MySQL on different ports via port and IP
   /usr/local/mysql/bin/mysql-u root-p-P 3306-h 127.0.0.1
   

Modify Login Password
MySQL does not have a password by default, the importance of increasing the password is self-evident.
usr/bin/mysqladmin-u root password ' new-password '
Format: Mysqladmin-u username-P Old password password new password

Show the structure of a data table

mysql> use Qqtexas;

mysql> desc tablesname;

To display the creation information for a table
Show CREATE TABLE tablesname;

Change the table structure to change the field type of the Zhangyan table username field to char (25)
ALTER TABLE Zhangyan change username username CHAR (25);

Create a database named Ocean

mysql> CREATE DATABASE Ocean;

mysql> drop Database Ocean;

mysql> drop tables Tablesname;

Empty records in a table
Mysql> Delete from Tablesname;

mysql> truncate TABLE db1.t1;

Import Mysql.sql from the current directory into the database
Mysql> source./mysql.sql;

Add PRIMARY key: ALTER TABLE name add primary key (column name)
Delete PRIMARY key: ALTER TABLE name drop PRIMARY key;
Renaming a column: ALTER TABLE name change Column Name property list

such as ALTER TABLE student change ID no char (4)
To add a column:
1 adding columns to the top of the table such as adding an id attribute to the student table at the front of the table
ALTER TABLE student Add ID int (4) NOT NULL Auto_increment primary key first;
2 after a column in a table, such as adding an id attribute to the Student table, put the first column no
ALTER TABLE student Add a int (one) afer no;
Delete a column: ALTER TABLE table name drop column name;
Change a column property such as Modify student No field properties ALTER TABLE student Modify no char (4);
Add index: ALTER TABLE name add index column name
Delete index: ALTER TABLE name DROP INDEX column name
Delete foreign key: ALTER TABLE name drop FOREIGN KEY constraint name
Use of limit:
SELECT * FROM table name limit 2;//If only one parameter is given, it indicates the maximum number of record rows returned
SELECT * FROM table name limit 1,3;//The first parameter specifies the offset of the first return record row, and the second parameter specifies the maximum number of records rows to return.

This article is from the "Start from the Heart" blog, please be sure to keep this source http://hao360.blog.51cto.com/5820068/1655572

MySQL Common Operations Command