MySQL installation and basic management commands and settings _ MySQL

This article mainly introduces the installation of MySQL and the basic management commands and settings, which are the basis for building the MySQL environment. For more information, see Install MySQL
Install Mysql on Linux/UNIX
On Linux, we recommend that you use the RPM Package to install Mysql. MySQL AB provides the following RPM packages:

• MySQL-MySQL server. You need this option unless you only want to connect to the MySQL server running on another machine.
• The MySQL-client-MySQL client program is used to connect to and operate the Mysql server.
• MySQL-devel-Library and inclusion files. if you want to compile other MySQL clients, such as the Perl module, you need to install this RPM package.
• MySQL-shared-This package contains the shared library (libmysqlclient. so *) that needs to be dynamically loaded by some languages and applications, using MySQL.
• MySQL-benchmark-MySQL database server benchmark and performance testing tool.

The following is an example of installing Mysql RMP on SuSE Linux. of course, this installation step is also suitable for other Linux systems that support RPM, such as Centos.

The installation procedure is as follows:
Log on to your Linux system as the root user.
Download the Mysql RPM Package: MySQL download.
Run the following command to install Mysql:

[root@host]# rpm -i MySQL-5.0.9-0.i386.rpm

The above process creates a mysql User and creates a mysql configuration file my. cnf.
You can find all MySQL-related binary files in/usr/bin and/usr/sbin. All data tables and databases will be created in the/var/lib/mysql directory.
The following is the installation process of some optional mysql packages. you can install them as needed:

[root@host]# rpm -i MySQL-client-5.0.9-0.i386.rpm[root@host]# rpm -i MySQL-devel-5.0.9-0.i386.rpm[root@host]# rpm -i MySQL-shared-5.0.9-0.i386.rpm[root@host]# rpm -i MySQL-bench-5.0.9-0.i386.rpm

Install Mysql on Window
Windows is relatively easy to install Mysql. you only need to download the Windows MySQL installation package and decompress the installation package.
Double-click the setup.exe file. next you only need to install the default configuration and click "next". by default, the installation information will be in the C: \ mysql directory.
Next, you can enter "cmd" command = "in the search box and switch to the C: \ mysql \ bin directory at the command prompt, and enter the following command:

mysqld.exe --console

If the installation is successful, the above commands will output some mysql startup and InnoDB information.
Verify Mysql installation
After Mysql is successfully installed, some basic tables are initialized. after the server is started, you can test it to verify that Mysql works properly.
Use the mysqladmin tool to obtain the Server Status:
Run the mysqladmin command to check the server version. on linux, the binary file is located in/usr/bin on linux and the binary file is located in C: \ mysql \ bin on window.

[root@host]# mysqladmin --version

On linux, the command will output the following results, which are based on your system information:

mysqladmin Ver 8.23 Distrib 5.0.9-0, for redhat-linux-gnu on i386

If no information is entered after the preceding command is executed, your Mysql installation is successful.
Use MySQL Client to execute simple SQL commands
You can use the MySQL command on the Mysql Client to connect to the mysql server. by default, the password of the Mysql server is blank, so you do not need to enter a password for this instance.
The command is as follows:

[root@host]# mysql

After the preceding command is executed, the mysql> prompt is output, indicating that you have successfully connected to the Mysql server. you can run the SQL command at the mysql> prompt:

mysql> SHOW DATABASES;

+----------+| Database |+----------+| mysql  || test   |+----------+2 rows in set (0.13 sec)

What needs to be done after Mysql installation
After Mysql is successfully installed, the default root user password is blank. you can use the following command to create a root user password:

[root@host]# mysqladmin -u root password "new_password";

Now you can use the following command to connect to the Mysql server:

[root@host]# mysql -u root -p

Enter password:*******

Note: The password is not displayed when you enter the password. you can enter the password correctly.
Start MySQL at Linux startup
If you need to start the MySQL server at Linux startup, you need to add the following command in the/etc/rc. local file:

/etc/init.d/mysqld start

Similarly, you need to add the mysqld binary file to the/etc/init. d/directory.

MySQL management
Start and close the MySQL server
First, run the following command to check whether the MySQL server is started:

ps -ef | grep mysqld

If MySql has been started, the above Command will output the mysql process List. if mysql is not started, you can use the following command to start the mysql server:

root@host# cd /usr/bin./mysqld_safe &

To disable the currently running MySQL server, run the following command:

root@host# cd /usr/bin./mysqladmin -u root -p shutdown

Enter password: ******

MySQL User settings
To add a MySQL user, you only need to add a new user to the user table in the mysql database.
The username and password of the instance to be added are guest and guest123. the user is authorized to perform SELECT, INSERT, and UPDATE operations:

root@host# mysql -u root -p

Enter password:*******

mysql> use mysql;

Database changed

mysql> INSERT INTO user      (host, user, password,       select_priv, insert_priv, update_priv)       VALUES ('localhost', 'guest',       PASSWORD('guest123'), 'Y', 'Y', 'Y');

Query OK, 1 row affected (0.20 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 1 row affected (0.01 sec)

mysql> SELECT host, user, password FROM user WHERE user = 'guest';

+-----------+---------+------------------+| host   | user  | password     |+-----------+---------+------------------+| localhost | guest | 6f8c114b58f2ce9e |+-----------+---------+------------------+1 row in set (0.00 sec)

When adding a user, use the PASSWORD () function provided by MySQL to encrypt the PASSWORD. You can see in the above instance that the user password is encrypted: 6f8c114b58f2ce9e.
Note: execute the flush privileges statement. After this command is executed, the authorization table is reloaded. If you do not use this command, you cannot use a new user to connect to the mysql server, unless you restart the mysql server.
When creating a user, you can specify the permission for the user. in the corresponding permission column, set the permission to 'y' in the insert statement. The user permission list is as follows:

• Select_priv
• Insert_priv
• Update_priv
• Delete_priv
• Create_priv
• Drop_priv
• Reload_priv
• Shutdown_priv
• Process_priv
• File_priv
• Grant_priv
• References_priv
• Index_priv
• Alter_priv

Another way to add a user is to use the SQL GRANT command. your command will add the user zara to the specified database TUTORIALS and the password is zara123.

root@host# mysql -u root -p password;

Enter password:*******

mysql> use mysql;

Database changed

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP  -> ON TUTORIALS.*  -> TO 'zara'@'localhost'  -> IDENTIFIED BY 'zara123';

The preceding command creates a user information record in the user table of the mysql database.
Note: the SQL statement of MySQL uses semicolon (;) as the end identifier.
/Etc/my. cnf file configuration
Generally, you do not need to modify the configuration file. the default configuration of this file is as follows:

[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock[mysql.server]user=mysqlbasedir=/var/lib[safe_mysqld]err-log=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

In the configuration file, you can specify directories for storing different error log files. Generally, you do not need to change these configurations.
Manage MySQL commands
The following lists common commands used to use Mysql databases:

• USE database name: Select the Mysql database to operate. after using this command, all Mysql commands are only for this database.
• Show databases: lists the DATABASES of the MySQL database management system.
• Show tables: displays all TABLES of the specified database. before using this command, you must use the use command to select the database to operate.
• Show columns from data table: displays data table attributes, attribute type, primary key information, whether it is NULL, default value, and other information.
• Show index from data table: displays detailed INDEX information of a data table, including the primary key (primary key ).
• Show table status like data TABLE \ G: this command outputs the performance and statistics of the Mysql database management system.