MySQL common operation MySQL backup and recovery

Log in to MySQL = = "Mysql-uroot-p View the version of the database" select version ();

See which libraries are available

Show datases; View which library is currently in select Database (); View the currently logged on user Select User (); View the table below a library; use DB; Show tables; View the fields of the table; Desc dong; Create a library creation database DB1; CREATE TABLE creation  table  TB1 (' id ' int (4), ' name ' char ())   Engine=myisam DEFAULT CHARSET=GBK; View build statement show create table TB1; Insert data inserts into TB1 (' id ', ' name ') VALUES (1, ' Jxcia ');   Here the Jxcia needs to be added ', because the name is defined by the char character. In addition the default can also not need to add (' id ', ' name '), if only want to add name, can do so. Insert into TB1 (' name ') value (' Jxcia ');    View the data just inserted  select * from tb1; or select *from tb1\g; update the contents of the datasheet updates db.tb1 set id=1 where Name= ' Lin '; Change the ID number named Lin in the Tb1 table below the DB Library to 1. or enter DB Library use DB first;  update tb1 set id=1 where Name= ' Lin '; empty table; truncate tables db1.tb1; drop table db1.tb1; Delete database drop databases db1; View Mys QL parameters; show varibales like ' max_connect% ';   Here like a grep% similar to a wildcard character. Modify the MySQL parameters, set global max_connnect_errors=100   temporarily modify the value of Max_connect_errors to 100, if you want to permanently take effect, you can modify the MY.CNF configuration file. Flush privileges   Refresh permissions view MySQL queue show processlist; Create user ' user1 ' @ ' host ' identified by '111 '; Create a regular user and authorize; grant all on *. * to User1  identified by ' 111 '; if local user grant all on * * to  [email protected]Identified by ' 111 '; Grant all on * * to ' user1 ' @ ' 192.168.2.48 ' identified by ' Lin '; * to ' user2 ' @ ' 192.168.2.% ' identified by ' Lin '; For users of a network segment Delete User
DROP USER ' username ' @ ' host ';
Repair TableRepair table TB1 executes the MySQL operation under the shell command Mysql-uroot-plin mysql-e "show tables" here MySQL refers to the library name-e after the option is the MySQL command. Second, MySQL backup and recovery 1,mysql backup Backup library take Discuz as an example Mysqldump-uroot-plin discuz >/data/discuz.sql Recovery Library mysql-uroot-plin discuz </d Ata/discuz.sql Backup table Mysqldump-uroot-plin discuz pre_forum_post >/data/pre_forum_post.sql Recovery table Mysql-uroot-plin Discu Z </data/pre_forum_post.sql discuz No need to add a table name, just add the data name. Specify character set Mysql-uroot-plin--default-character-set=gbk discuz >/data/discuz.sql Mysql-uroot-plin--default-char when backing up and recovering ACTER-SET=GBK Discuz </data/discuz.sql

MySQL common operation MySQL backup and recovery