MySQL daily command

Daily operation of MySQL

Mysqladmin-u root-p password "abcabc" sets a password for the MySQL root account

If you have set the password before, enter the original password. If not, enter.

Log in to mysql-uroot-p

Authorize remote login:

Grant all privileges on. To 'root'@'%' identified by' abcabc' with grant option

Mysql > show databases; # View all databases

Mysql > create database name; # create a new database named name

Mysql > use name;# comes into use

Mysql > create table users (id int NOT NULL,user_name CHAR (16) NOT NULL,user_passwd CHAR (48) DEFAULT', PRIMARY KEY (id)); # create a table named users

Mysql > desc users; # View table structure

Mysql > insert into users (id,user_name,user_passwd) values (1 Magneto Zhangsanqie password (123123)); # plus password allows input to be displayed in ciphertext, otherwise plaintext

Query OK, 1 row affected, 1 warning (0.00 sec)

Mysql > insert into users (id,user_name,user_passwd) values (2gr) Lisijia Magna 123123')

Query OK, 1 row affected (0.00 sec)

Mysql > insert into users values (3 recordings wangwuqiqiao 123abc')

Query OK, 1 row affected (0.01sec)

Mysql > insert into users values (4 legendary jackhammer dint password ('123abc'))

Query OK, 1 row affected, 1 warning (0.00 sec)

Mysql > select * from users; view the information in the table

Update users set user_passwd='123' where id='1'; # modify information in the table

Mysql > delete from users where id='4'; # Delete id=4 's record

Mysql > drop table users; # Delete tables

Mysql > drop database name;# delete database

Backup

There are three types of MySQL backups:

Full backup: every backup will back up all the contents of the database, and the backup quantity is getting larger and larger.

Differential backup: with reference to the most recent full backup, new and changed content will be backed up each time, and the backup volume will become larger and larger.

Incremental backup: with reference to all previous backups, only the added content will be backed up each time, with a small amount of backup.

The full backup is described as follows:

View pre-backup data

Exit the database and use mysqldump backup

Mysqldump-uroot-p-- databases name > name.sql # backup database and table data

After the backup is complete, delete the name database

Enter the password to import

Overall database backup

[root@lin3036] # mysqldump-u root-p-- all-databases > all.sql

The backup table structure is also more practical, eliminating the creation of the table structure.

[root@lin3036 ~] # mysqldump-u root-p-d name users > jiegou.sql # back up the users table structure in the name database to the current directory jiegou.sql