Published Book on Amazon
 |
All of IOT Starting with the Latest Raspberry Pi from Beginner to Advanced – Volume 1 |
 |
All of IOT Starting with the Latest Raspberry Pi from Beginner to Advanced – Volume 2 |
출판된 한글판 도서
 |
최신 라즈베리파이(Raspberry Pi)로 시작하는 사물인터넷(IOT)의 모든 것 – 초보에서 고급까지 (상) |
 |
최신 라즈베리파이(Raspberry Pi)로 시작하는 사물인터넷(IOT)의 모든 것 – 초보에서 고급까지 (하) |
Original Book Contents
24.2.3 MySQL Management Tool
24.2.3.1 <MySQL Administrator> – mysqladmin command
<MySQL Administrator> is a management-dedicated utility program used to manage the MySQL database system on the whole. This command is used in a noninteractive manner.
This program is automatically installed on the computer where <MySQL Server> is installed. However, if you want to remotely access a system with a MySQL database from another computer on the network and perform management works, you need to download a appropriate program for the operating system of the remote computer and install it separately.
When installing this tool on Raspberry Pi, the following command can be used.
| sudo apt-get install mysql-admin |
This program is executed by "mysqladmin" command. The basic format of the command is as follows.
[Command Format]
| mysqladmin -u<user-id> -p[password] [options] command command.... |
[Detail Description]
■ In the command options, user and password must be specified.
[Main option]
| --help, -? | Display a help message and exit. |
| --host=host_name, -h host_name | Connect to the MySQL server on the given host. |
| --user=user_name, -u user_name | The MySQL user name to use when connecting to the server. |
| --password[=password], -p[password] | The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. If you omit the password value following the --password or –p option on the Shell, mysql prompts for one. Specifying a password on the Shell should be considered insecure. See Section 6.1.2.1, "End-User Guidelines for Password Security". You can use an option file to avoid giving the password on the Shell. |
The main functions that can be performed here are as follows.
| command | Description | Remarks |
| create databasename | Create a new database | Create database |
| debug | Instruct server to write debug information to log |
|
| drop databasename | Delete a database and all its tables | Delete database |
| extended-status | Gives an extended status message from the server | View detail info. |
| flush-hosts | Flush all cached hosts |
|
| flush-logs | Flush all logs |
|
| flush-status | Clear status variables |
|
| flush-tables | Flush all tables |
|
| flush-threads | Flush the thread cache |
|
| flush-privileges | Reload grant tables (same as reload) |
|
| kill id,id,... | Kill mysql threads | Stop user connection |
| password [new-password] | Change old password to new-password in current format | Change user password |
| old-password [new-password] | Change old password to new-password in old format
| Change user password |
| ping | Check if mysqld is alive |
|
| processlist | Show list of active threads in server | Check connected users |
| reload | Reload grant tables |
|
| refresh | Flush all tables and close and open logfiles |
|
| shutdown | Take server down | Terminate MySQL |
| start-slave | Start slave |
|
| stop-slave | Stop slave |
|
| status | Gives a short status message from the server | View summary info. |
| variables | Prints variables available | Check environment |
| version | Get version info from server | View version info. etc. |
24.2.3.2 <MySQL Shell> – "mysql" command
<MySQL Shell> is a program that connects to MySQL database and manages the data in the database. It is compared that main purpose of <MySQL Administrator> is mainly to manage MySQL database system itself.
The main functions provided by this program are as follows.
■ Checking and changing the status of MySQL Shell
■ Checking various information about database
■ Creating and deleting database
■ Checking various information about table
■ Manipulating data in tables in a database
This program is automatically installed on the computer when <MySQL database> server is installed. However, if you want to remotely connect to a system with a MySQL database from another computer on the network, you need to download the appropriate program for the operating system of the remote computer and install it separately.
When installing this program on Raspberry Pi, the following command can be used.
| sudo apt-get install mysql-client |
There are two types of commands that can be executed in <MySQL Shell>: MySQL Shell command and standard database SQL command.
MySQL Shell command is used to check the current state of <MySQL shell> or to influence the future behavior and presentation of the Shell. This command does not directly affect datas that are stored in the database.
Standard database SQL command is a command to directly manipulate data stored in the database. These include commands to define database where data is stored, commands to define data to be stored, and commands to input and output data.
MySQL Shell command and database SQL commands are discussed in detail later.
24.2.3.3 <MySQL Backup> – "mysqldump" command
<MySQL Backup> is a management utility program that is used to backup the data in the MySQL database system. This command is used in a noninteractive manner.
This program is automatically installed on the computer where <MySQL Server> is instaled. However, if you want to remotely connect a system with a MySQL database from another computer on the network, you need to download the appropriate program for the operating system of the computer and install it separately.
This program is executed by "mysqldump" command. The basic format of the command is as follows.
[Command Format]
| mysqldump -u<user-id> -p[password] [option] [database-name] [table-name] |
[Detail Description]
■ In this command options, user and password must be specified.
[Main option]
| --help, -? | Display a help message and exit. |
| --host=host_name, -h host_name | Connect to the MySQL server on the given host. |
| --user=user_name, -u user_name | The MySQL user name to use when connecting to the server. |
| --password[=password], -p[password] | The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. If you omit the password value following the --password or –p option on the Shell, mysql prompts for one. Specifying a password on the Shell should be considered insecure. See Section 6.1.2.1, "End-User Guidelines for Password Security". You can use an option file to avoid giving the password on the Shell. |
| --all-databases, -A | Dump all tables in all databases. This is the same as using the --databases option and naming all the databases on the Shell. |
| --all-tablespaces, -Y | Adds to a table dump all SQL statements needed to create any tablespaces used by an NDBCLUSTER table. This information is not otherwise included in the output from mysqldump. This option is currently relevant only to MySQL Cluster tables. |
24.2.3.4 <MySQL Import> – "mysqlimport" command
<MySQL Import> is a management-dedicated utility program used to restore data in a MySQL database system. This command is used in a noninteractive manner.
This program is automatically installed on the computer that MySQL server is installed. However, if you want to remotely connect a system with a MySQL database from another computer on the network, you need to download the appropriate program for the operating system of the computer and install it separately.
This program is executed by "mysqlimport" command. The basic format of the command is as follows.
[Command Format]
| mysqlimport -u<user-id> -p[password] [option] <database-name> <file-name> |
[Detail Description]
■ In this command options, user and password must be specified.
[Main option]
| --help, -? | Display a help message and exit. |
| --host=host_name, -h host_name | Connect to the MySQL server on the given host. |
| --user=user_name, -u user_name | The MySQL user name to use when connecting to the server. |
| --password[=password], -p[password] | The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. If you omit the password value following the --password or –p option on the Shell, mysql prompts for one. Specifying a password on the Shell should be considered insecure. See Section 6.1.2.1, "End-User Guidelines for Password Security". You can use an option file to avoid giving the password on the Shell. |
| --delete, -D | Empty the table before importing the text file. |
| --force, -f | Ignore errors. For example, if a table for a text file does not exist, continue processing any remaining files. Without --force, mysqlimport exits if a table does not exist. |
| --lock-tables, -l | Lock all tables for writing before processing any text files. This ensures that all tables are synchronized on the server. |