MySQL related knowledge

Set MySQL password
SET PASSWORD = PASSWORD (' [email protected] ');

Setting up a MySQL remote connection
GRANT all privileges on * * to ' root ' @ ' 192.168.4.0 ' identified by ' [email protected] ' with GRANT OPTION;

Forgot MySQL Password
UPDATE mysql.user SET password=password (' 123456 ') where user= ' root ';
Flush privileges;
Exit

Back up the MySQL database
Mysqldump-u root-p mysql > mysql.6.13
After you delete a database, you must create a new library to recover it, or it will be reported without this database
Recovering a Database
MySQL--user root--password=123456 mysql < mysql.6.13

Scheduled Tasks scheduled backups
Mkdir-p/root/mysql_dump/data
Cd/root/mysql_dump
Touch mysql_back.sh
chmod 755 mysql_back.sh

# Edit Backup Script
Vim mysql_back.sh

############### #下面是备份脚本的内容
#!/bin/sh
# File:/root/mysql_dump/mysql_back.sh
# Database Info
Db_name= "Cncounter"
Db_user= "Root"
Db_pass= "Mypasssecret"
# Others VARs
# Whereis Mysqldump
# is ' and not '
Bin_dir= "/usr/bin"
Bck_dir= "/root/mysql_dump/data"
Date= ' Date +%y%m%d_%h%m%s '
# TODO
Mkdir-p $BCK _dir
$BIN _dir/mysqldump--opt-u$db_user-p$db_pass $DB _name \
> $BCK _dir/$DB _name.dump_$date.sql

If the backed up file is large, you can use gzip compression
/usr/bin/mysqldump--opt-uroot-pmypasssecret Cncounter | GZIP \
>/root/mysql_dump/data/cncounter.dump_ ' Date +%y%m%d_%h%m%s '. sql.gz

Add to Crontab-e
1 1 * * */root/mysql_dump/mysql_back.sh

Backing up a database using mysqldump

1. Backing up a database

mysqldump--user [user name]--password=[password] [database name] > [dump file]
Or
Mysqldump-u[user name]-p[password] [database name] > [dump file]

Example:

mysqldump--user root--password=myrootpassword db_test > Db_test.sql
Or
Mysqldump-uroot-p Myrootpassword db_test > Db_test.sql

2. Backing up multiple databases

Mysqldump-u[user name]-p[password] [database name 1] [database name 2]. > [Dump file]

Example:

mysqldump--user root--password=myrootpassword db_test db_second db_third > Db_test.sql

3. Back up all databases

mysqldump-u [user Name]-p [password]--all-databases > [dump file]

4. Back up a table

Mysqldump--user [username]--password=[password] [database name] [table name]/tmp/sugarcrm_accounts_contacts.sql

Example:

mysqldump--user Root--password=myrootpassword db_test customers Db_test_customers.sql

5. Recovering a Database

MySQL--u [username]--password=[password] [database name] < [dump file]

Example:

MySQL--user root--password=myrootpassword new_db < Db_test.sql

Compress the MySQL database directly backup

Mysqldump-hhostname-uusername-ppassword DatabaseName | gzip > backupfile.sql.gz
Back up a MySQL database (some) table

Mysqldump-hhostname-uusername-ppassword databasename specific_table1 specific_table2 > Backupfile.sql
Back up multiple MySQL databases at the same time

Mysqldump-hhostname-uusername-ppassword–databases databasename1 databasename2 databasename3 > Multibackupfile.sql
Backing up the database structure only

Mysqldump–no-data–databases databasename1 databasename2 databasename3 > Structurebackupfile.sql
Back up all databases on the server

Mysqldump–all-databases > Allbackupfile.sql
Commands to restore MySQL database

Mysql-hhostname-uusername-ppassword DatabaseName < Backupfile.sql
Restore a compacted MySQL database

Gunzip < backupfile.sql.gz | Mysql-uusername-ppassword DatabaseName

8. Back up multiple databases at a time
MySQL dump-uroot-p123456--databases db1 DB2 >f:\muldbs.sql
Restore partial (1) MySQL command line source method and (2) system command line method
1. Restore all databases:
(1) MySQL command line: MySQL >source f:\all.sql
(2) System command line: mysql-uroot-p123456 <f:\all.sql
2. Restore a single database (Specify a database)
(1) MySQL >use mydb
MySQL >source f:\mydb.sql
(2) mysql-uroot-p123456 mydb <f:\mydb.sql
3. Restore multiple tables for a single database (Specify a database)
(1) MySQL >use mydb
MySQL >source f:\multables.sql
(2) mysql-uroot-p123456 mydb <f:\multables.sql
4. Restore multiple databases (a backup file with multiple databases, no database required at this time)
(1) MySQL command line: MySQL >source f:\muldbs.sql
(2) System command line: mysql-uroot-p123456 <f:\muldbs.sql

MySQL related knowledge