MySQL Management tools

One, MySQL tools

Second, mysqladmin tools

Third, mysqldump tools (for Backup)

One, MySQL tools

MySQL is a simple command-line SQL tool that supports both interactive and non-interactive runs

Examples of Use:
1. Connect to the server using the root account and enter the MySQL database by default

MySQL [db_name]-u root-p# Enter password prompt

MySQL [db_name]-u root-p ' 123 ' #直接使用密码123登录

MySQL [db_name]-u root-p-H 192.168.0.254# Use the root account to connect 192.168.0.254 server, enter the password prompt

2. Execute script file using MySQL tool

[email protected] ~]# cat script.sql Show tables; [[email protected] ~]# mysql MySQL < script.sql > out.tab-u root-p #执行结果输出重定向到out. Tabenter password: [[email PR Otected] ~]# cat out.tab #查看执行结果Tables_in_mysqlcolumns_privdb ...

Second, mysqladmin tools

Mysqladmin is a tool for performing administrative operations

Syntax format:

mysqladmin [Options] command [command arguments] [command [command parameters]]

Common commands, command parameters

Create db_name creating a database named Db_name

Drop db_name Delete the database named db_name and all data tables in the database

Extended-status displaying server state variables and variable values

Flush-hosts refreshing cache information for all hosts

Flush-logs Refresh All the logs

Flush-privileges Reload Permission data table

Flush-status emptying state variables

Flush-tables Refresh all data tables

Kill Id,id kills the server process

Password New-pass setting a new password

Ping checks if the server is available

Reload Reload Permission data table

Refresh refreshes all data tables and restarts log files

Shutdown shutting down the server

Start-slave initiating replication from the server

Stop-slave stopping replication from the server

Third, mysqldump tools

Mysqldump is a database logical backup program that we can use to back up one or more MySQL databases or to transfer the database to another MySQL server. When executing mysqldump, the account must have SELECT permission to back up the data tables, SHOW View permissions are used for backup views, and Reigger permissions are used for backup triggers. Some command options may require more permissions to complete the operation. Mysqldump is not a solution for big data backup, because mysqldump needs to rebuild the SQL statement to implement the backup function, the data volume is relatively large database backup and restore operations, the speed is slower. Opening the mysqldump backup file will reveal that the database is actually reproduced in SQL language.

For large-scale data backup and restore, the use of physical backup will be more appropriate, by directly replicating data files, you can achieve rapid data restoration work. Providing a mysqlbackup tool in the enterprise version of MySQL is also a solution that can be considered, providing a variety of different MySQL database engines with high performance backup and restore capabilities.

You can use mysqldump to back up some of the data tables in the database, or you can back up the entire database (do not use the data table name after the database), and you can back up all the databases in the MySQL system. For database files backed up using the Mysqldump tool, you can use the MySQL command tool to restore data.

The syntax format is as follows:
mysqldump [Options] db_name [table_name]
mysqldump [options]--databases db_name ...
mysqldump [Options]--all-databases

Common options:

--add-drop-database Adding and removing SQL statements from the same database in the backup file

--add-drop-table Adding and removing SQL statements from the same data table in the backup file

--add-deop-trigger Adding and removing SQL statements for the same trigger in the backup file

--add-locks adding table locks and shackles SQL statements before and after backing up a data table

--all-databases backing up all data tables in all databases

--apply-slave-statements adding a STOP slave statement before change master

--bind-address=ip_address connection to MySQL server using specified network interface

--comments adding comments to a backup file

--create-options includes all MySQL features in the CREATE TABLE statement

--databases backup of several specified databases

--debug Creating debugging Logs

--default-character-set=charsename Setting the default character set

--host,-h setting up hosts that need to be connected

--ignore-table set up a data table that does not need to be backed up, which can be used multiple times

--lock-all-tables set global lock, lock all data tables to ensure the integrity of backup data

--no-create-db,-n export only data without creating a database

--no-create-info export only data without creating a data table

--no-date back-up data content for backing up table structures

--password,-pshiyong using a password to connect to the server

--port=port_num connecting the server with the specified port number

--replace Replace insert with replace statement

Example:

Back up all databases

Mysqldump-u root-p--all-databases > All_database_sql

Back up the MySQL database

Mysqldump-u root-p--database mysql > Mysql_database_sql

Back up the user under MySQL database

Mysqldump-u root-p MySQL user > user_table

Restoring a database using a All_database_sql database backup file

Mysql-u Root-p < All_database_sql

Restoring a database using a Mysql_database_sql database backup file

Mysql-u Root-p < Mysql_database_sql

Restoring a database using a user_table database backup file

Mysql-u Root-p < user_table

This article is from the "Arvin Lau" blog, be sure to keep this source http://64314491.blog.51cto.com/2784219/1674115

MySQL Management tools