MySQL backup restoration and some common operation statements

Query the number of cards generated on a specified date: Select count (*) from carddata where make_date> = to_date ('2017-04-18 ', 'yyyy-mm-dd'); display all databases: show databases; show all database tables: show tables; restore a database: mysql-H localhost-u root-p123456 wwwuse target database name, as shown in the command line I entered: mysql> use news; 6. Import file: the file name imported from mysql> source, for example, mysql> source news. SQL; MySQL backup and restoration are completed using mysqldump, MySQL, and source commands. 1. mySQL backup and restoration 1.1 backup Start Menu under Win32 | run | cmd | use the "CD/program files/MySQL Server 5.0/bin" command to enter the bin folder | use "mysqldump- U username-P databasename> exportfilename "to export the database to a file, for example, mysqldump-u root-P voice> voice. SQL, and then enter the password to start exporting. 1.2 restore to MySQL command line client, enter the password, go to "mysql>", enter the command "show databases;", Press enter to see what database; create the database you want to restore, enter "create database voice;", Press ENTER; switch to the database you just created, enter "use voice;", Press ENTER; import data, and enter "Source voice. SQL; ", Press enter to start importing, and" mysql> "is displayed again. If no error is returned, the restoration is successful. 2. MySQL backup and restoration in Linux [root @ localhost ~] # Cd/var/lib/MySQL (go to the MySQL database directory and adjust the directory based on your MySQL installation) [root @ localhost MySQL] # mysqldump-u root-P voice> voice. SQL, enter the password. 2.2 Reduction Method 1: [root @ localhost ~] # Mysql-u root-P press enter and enter the password to go To the MySQL console "mysql>", which is restored in the same way as 1.2. Method 2: [root @ localhost ~] # Cd/var/lib/MySQL (go to the MySQL database directory and adjust the directory based on your MySQL installation) [root @ localhost MySQL] # mysql-u root-P voice backupfile. the SQL backup MySQL database is in the format of a table with deletion. the MySQL database is in the format of a table with deletion. This allows the backup to overwrite the existing database without the need to manually delete the original database. Mysqldump -- add-drop-table-uusername-ppassword databasename> backupfile. SQL directly compresses and backs up the MySQL database mysqldump-hhostname-uusername-ppassword databasename | gzip> batch back up a MySQL database table mysqldump-hhostname-uusername-ppassword databasename specific_table1 batch> backupfile. SQL simultaneously backs up multiple MySQL Databases mysqldump-hhostname-uusername-ppassword-databases databasename1 databasename2 databasename3> multibackupfile. SQL only backs up the database structure mysqldump-no-data-databases databasename1 databasename2 databasename3> structurebackupfile. all databases on the SQL Backup Server mysqldump-all-databases> allbackupfile. SQL command for restoring MySQL database mysql-hhostname-uusername-ppassword databasename <backupfile. SQL restore compressed MySQL database gunzip <backupfile. SQL .gz | mysql-uusername-ppassword databasename transfers the database to the new server mysqldump-uusername-ppassword databasename | mysql-host = *. *. *. * Common Use Cases for-C databasename: 1. export mysqldump-u username-P Database Name> exported file name mysqldump-u root-P dataname> dataname. at this time, you will be prompted to enter the password of the root user name. after entering the password, the dataname database will be successfully backed up in the MySQL/bin/directory. 2. export a table mysqldump-u user name-P database name Table Name> exported file name mysqldump-u root-P dataname users> dataname_users. SQL 3. export a database structure mysqldump-u wcnc-p-D-add-drop-Table smgp_1__wcnc> D: /wcnc_db. SQL-d no data-add-drop-table add a drop Table 4 before each create statement. import common source commands to the MySQL Database Console, such as MySQL-u root-P mysql> Use Database, and then use the source command. The following parameters are script files (as used here. SQL) mysql> source D:/wcnc_db. SQL