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

Source: Internet
Author: User
Tags mysql backup

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.