MySQl Common operations

3.2. mysql forgot root password, reset root password

1. If no password is set, set the root password first

# mysqladmin-uroot password ' 123456 '

# mysql-uroot-p

2. When you forget your MySQL password, reset your MySQL password

# vim/etc/my.cnf # # in the [Mysqld] field add

Skip-grant

#/etc/init.d/mysqld Restart

# Mysql-uroot

mysql> use MySQL;

mysql> Update user Set Password=password (' Qweasdzxc ') where user= ' root ';

# vim/etc/my.cnf # # Remove Skip-grant

3.3. mysql Login

1. telnet to MySQL

# mysql-uroot-p3306-pqweasdzxc-h127.0.0.1

2, MySQL authorized to remote IP login

Mysql> Grant All on * * to ' user ' @ ' percent ' identified by ' 123456 ';

Mysql> Grant All on * * to ' user2 ' @ ' 192.168.230.128 ' identified by ' 123456 ';

mysql> user MySQL;

Mysql> select User,password,host from user; # # View all MySQL users and licensing conditions

telnet to 192.168.230.130 MySQL

# mysql-uuser-p ' 123456 '-h192.168.230.130

3. Multiple local MySQL Logins

# mysql-uroot-s/tmp/mysql.sock-p

3.4. MySQL Common operation

1. See what databases are available

mysql> show databases;

2, switch, enter the database

mysql> use MySQL;

3. View the current database

Mysql> Select Database ();

4. View the currently logged in user

Mysql> Select User ();

5. View current data to see version

Mysql> select version ();

6. View the table where the data is currently located

Mysql> Show tables;

7. View the fields of a table in the current database

mysql> desc User;

8. View the table statement for a table in the current database

Mysql> Show CREATE TABLE user\g;

9. Create a database table

(1) mysql> CREATE table t4 ('id ' int (4), 'name' char (40));

(2) mysql> CREATE TABLE TB1 (

ID int (4),

name varchar (40)

) Engine=myisam DEFAULT CHARSET=GBK;

# # ID table in data first column name, Name table in data second column names

Example:mysql> CREATE TABLE tb5 ('nianling' int (4), 'Dianhua' char (40));

mysql> desc TB5;

+----------+----------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------+----------+------+-----+---------+-------+

| nianling | Int (4) |     YES | |       NULL | |

| Dianhua | CHAR (40) |     YES | |       NULL | |

+----------+----------+------+-----+---------+-------+

10, inserting data into the table;

mysql> INSERT INTO tb5 (nianling,Dianhua) VALUES (20, ' 10086 ');

11. Query table Data

Mysql> select * from Tb5;

+----------+---------+

| nianling | Dianhua |

+----------+---------+

| 20 | 10086 |

+----------+---------+

1 row in Set (0.00 sec)

12. Update data

mysql> Update tb5 set nianling= ' cc ' where dianhua=1;

13. Delete the specified line

Mysql> Delete from tb5 where nianling=20;

Mysql> select * from Tb5;

Empty Set (0.00 sec)

14. Clear a table, keep the table structure

mysql> truncate TABLE tb5;

16. Delete a table

mysql> drop table TB2;

17. Delete Database

mysql> drop database test;

3.5, MySQL common operation 2

1. Create a MySQL user

Mysql> Grant All on * * to ' user5 ' @ ' localhost ' identified by ' 123456 ';

Mysql> Grant All on * * to ' user6 ' @ ' 192.168.230.128 ' identified by ' 123456 ';

Grant: Authorization

All: permissions, additions and deletions

*. *: The first * indicates a database, the second * represents the table under the database

USER5,USER6 represents the user name

LOCALHOST,192.168.230.128: Indicates the source IP of the login, or it can be set to 192.168.230.% to allow the source IP of the 192.168.230 segment, or a single% to allow all IP

2. Refresh Permissions

mysql> flush Privileges;

3. View the processes currently in use

Mysql> show Processlist;

4. View variables

Mysql> Show variables; # # All variables

Mysql> Show variables like ' max_connect% '; # #查看以 Max_connect The variable that starts with the% pass

5. Modifying variables

mysql> set global max_connect_errors=100;

This setting method is only temporary settings, if you restart the MySQL service, just set will be invalid, want to take effect permanently, then you should set the max_connect_errors to the MySQL configuration file in/etc/my.cnf

6. View status information

Mysql> Show status; # #查看所有状态信息

Mysql> Show status like '%_rows '; # #查看以 _rows end of status information% pass

7, production see MySQL error log

# ll/data/mysql/

# tail-f/data/mysql/master1.err

# tail-f/data/mysql/localhost.localdomain.err

8. Repair MySQL Table

mysql> Repair table db.tb1; # # DB Library name TB1 is the name of the table in the DB Library tb1

3.7. mysql Backup and recovery

1. Backup Library:

# mysqldump-uroot-p mysql >./mysql20180330.sql # #输入root密码, backup successful

MySQL Backup appears warning

# mysqldump-uroot-p mysql >./mysql20180330.sql

Enter Password:

--warning:skipping The data of table mysql.event. Specify the--events option explicitly.

Workaround

# mysqldump-uroot-p --events--ignore-table=mysql.event mysql > Mysql20180330.sql

2. Recovery library:

# mysql-uroot-p MySQL < mysql20180330.sql # #输入密码, restore success

3. Backup table

# mysqldump-uroot-p MySQL db >/mysql.db20180330.sql

4. Recovery form

# mysql-uroot-p MySQL < mysql.db20180330.sql

5. Character Set backup

# mysqldump-uroot-p--DEFAULT-CHARACTER-SET=GBK mysql >./mysqlzifu20180330.sql

A warning prompt appears

# mysqldump-uroot-p--DEFAULT-CHARACTER-SET=GBK mysql >./mysqlzifu20180330.sql

--warning:skipping The data of table mysql.event. Specify the--events option explicitly.

Solutions

Mysqldump-uroot-p --events--ignore-table=mysql.event --default-character-set=gbk mysql >./ Mysqlzifu20180330.sql

6. Recovery

# mysql-uroot-p--DEFAULT-CHARACTER-SET=GBK MySQL < mysqlzifu20180330.sql

MySQl Common operations