Mysql common operations: mysql backup and recovery, mysql backup and recovery

Mysql common operations: mysql backup and recovery, mysql backup and recovery
Log on to mysql = mysql-uroot-p to view the database version select version ();

View Databases

Show datases; view the current database select database (); view the current logon user select user (); View tables under a database; use db; show tables; view table fields; desc dong; create database db1; create table tb1 ('id' int (4), 'name' char (40) ENGINE = myisam default charset = gbk; view the table creation statement show create table tb1; insert data into tb1 ('id', 'name') values (1, 'jxcia '); here jxcia needs to add '', this is because the name is defined as a char character. In addition, you do not need to add ('id', 'name') by default. If you only want to add name, you can. Insert into tb1 ('name') value ('jxcia '); view the newly inserted data select * from tb1; or select * from tb1 \ G; update the data table content update db. tb1 set id = 1 where name = 'line'; change the id number of the tb1 table named lin in the database to 1. Alternatively, you can first enter the database use db; update tb1 set id = 1 where name = 'lin'; clear the table; truncate table db1.tb1; Delete the table; drop table db1.tb1; delete database drop database db1; view mysql parameters; show varibales like 'max _ connect % '; here, like is similar to grep %, similar to a wildcard. Modify the mysql parameter; set global max_connnect_errors = 100 temporarily modify the value of max_connect_errors to 100. To take effect permanently, modify the configuration file of my. cnf. Flush privileges refresh permission to view the mysql queue show processlist; create a common user create user 'user1' @ 'host' identified by '000000'; create and authorize a common user; grant all on *. * to user1 identified by '20140901'; if it is a local user grant all on *. * to user1 @ localhost identified by '20140901'; grant all on *. * to 'user1' @ '192. 168.2.48 'identified by 'line'; grant all on db1. * to 'user2' @ '2017. 168.2.% 'identified by 'lin'; for users in a CIDR Block Delete a user
Drop user 'username' @ 'host ';
Repair table tb1 execute mysql operations under the shell command mysql-uroot-plin mysql-e "show tables" Here mysql refers to the options after database name-e is a mysql command. Ii. mysql backup and recovery 1. The mysql backup and backup database uses discuz as an example: mysqldump-uroot-plin discuz>/data/discuz. SQL restoration database mysql-uroot-plin discuz </data/discuz. SQL backup table mysqldump-uroot-plin discuz pre_forum_post>/data/pre_forum_post. SQL recovery table mysql-uroot-plin discuz </data/pre_forum_post. SQL discuz no longer requires adding a table name, you only need to add the data name. Mysql-uroot-plin -- default-character-set = gbk discuz>/data/discuz. SQL mysql-uroot-plin -- default-character-set = gbk discuz </data/discuz. SQL