8.2 MySQL Management
There are some useful tools in the MySQL release that make it easier to manage, the most common of which is the Mysqladmin program.
8.2.1 command except for the mysqlshow command, all MySQL commands accept the following 3 standard parameters.
Command option parameter description
-u username by default, the MySQL tool attempts to use the current Linux username as the user name for MySQL, and the-u parameter can specify a different user name.
-p password If the-p parameter is given but no password is provided, the system prompts for a password.
-h hostname is used to connect servers located on different hosts (this parameter can always be omitted for local servers)
The 1.MYISAMCHK command Myisamchk tool is designed to check and repair any data table using the default MyISAM table format, and the MyISAM table is supported by MySQL itself. Typically, MYISAMCHK should run as a MySQL user created at installation time, and the command should be run in the same directory as the data table. In order to check the database, execute the command su MySQL first, and then change the directory to the directory corresponding to the database name. For example:
MYISAMCHK-E-R *. MYI
The most common command options for MYISAMCHK are as follows:
Command option Description
-C Checklist to find errors
-e Perform an extended check
-R Fix found error
2.mysql command This is a major and powerful command-line tool for MySQL. Almost every administrative or user level can be executed here. You can start MySQL from the command line by adding the database name as a parameter at the end of the command line, eliminating the need to use the use<database> command in MySQL's console. For example, the command to start the console with user name Rick, prompting for a password, and using Database foo By default is as follows:
$mysql-U rick-p foo
If you do not specify a database when you start MySQL, you can use the use<databasename> option in MySQL to select a database.
In addition, you can run MySQL in non-interactive mode by simply binding the command to an input file and reading it from the command line. In this case, you must specify the password on the command line:
$mysql-u Rick--password=secretpassword foo < Sqlcommands.sql
Once MySQL reads and finishes processing the command, it returns to the command prompt.
MySQL server supports the idea of having many different databases at the same time, all databases are serviced and managed by the same server process. Each database is a basic, separate set of tables. This allows you to set up different databases for different purposes and specify different users for each database, and you can manage them efficiently by using the same database server. As long as you have the appropriate permissions, you can switch between different databases by using the use command.
A specific database MySQL is created automatically by the MySQL installation, and it is used to save data such as users and permissions.
3.mysqladmin This is a quick tool for MySQL database management. In addition to the common parameters, the following commands are supported:
Command description
Create <databasename> creating a new database
Drop <databasename> Delete a database
Password <new_password> Change Password
Ping checks if the server is running
Reload overload control permissions for the grant table
Status provides the state of the server
Shutdown Stop Server
Variable displays variables that control MySQL operations and their current values
Version provides the number of the server and the time it continues to run
If you call the mysqladmin command without parameters, you can see the complete list of options from the command prompt.
4.mysqlbug5.mysqldump This is an extremely useful tool that allows you to export part or entire databases to a single file as a set of SQL commands, which can be re-imported into MySQL or other SQL RDBMS. It takes the standard user and password information as parameters, and also accepts the database name and table name as parameters.
Command description
--add-drop-table add SQL command to output file to discard (delete) any table before creating a table command
-E uses the extended insert syntax.
-T only dumps the data in the table, not the information used to create the table
-D only dumps the table structure, not the actual data
This tool is useful for migrating data or fast backups. In addition, due to the way MySQL's client segment server is implemented, it can even be used to implement remote backups by using a mysqldump client installed on a different machine. The following example shows an example of a database myplaydb that is connected via user name Rick:
$ mysqldump-u rick-p myplaydb > Myplaydb.dump
The 6.mysqlimportmysqlimport command is used to import data into a table in batches, and a large amount of text data can be read from an input file by using Mysqlimport. The only parameter requirement for this command is a file name and a database name.
7.mysqlshow This gadget is used to quickly learn about MySQL installation and its constituent database
1. No parameters are provided, it lists all available databases
2. Take a database as a parameter, which lists the tables in that database
3. With the database and table name as parameters, it lists the columns in the table
4. With database, table, and column parameters, it lists the details of the specified column
Linux Programming--mysql Management
