Mysql installation and basic management commands and Settings _mysql

MySQL Installation
Install MySQL on Linux/unix
The Linux platform recommends the use of the RPM package to install Mysql,mysql AB provides the following RPM package download address:

• Mysql-mysql server. You need this option unless you only want to connect to a MySQL server running on another machine.
• Mysql-client-mysql client program for connecting and operating the MySQL server.
• Mysql-devel-Libraries and include files, if you want to compile other MySQL clients, such as the Perl module, you need to install the RPM package.
• Mysql-shared-This package contains a shared library (libmysqlclient.so*) that some languages and applications need to dynamically load, using MySQL.
• Mysql-bench-mysql the baseline and performance testing tools for the database server.

The following instance of installing the MySQL RMP is done on the SUSE Linux system, which is also suitable for other Linux systems that support RPM, such as: Centos.

The installation steps are as follows:
Log on to your Linux system using root user.
Download mysql rpm package, download the address for: MySQL download.
Use the following command to perform MySQL installation, RPM package for you to download the RPM package:

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

The process of installing the MySQL server above will create a MySQL user and create a MySQL profile my.cnf.
You can find all the MySQL-related binaries in/usr/bin and/usr/sbin. All data tables and databases will be created in the/var/lib/mysql directory.
Here are some MySQL optional package installation process, you can install according to your own needs:

[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 Windows
installing MySQL on Windows is relatively straightforward, and you only need to download the Windows version of the MySQL installation package in the MySQL download and unpack the installation package.
Double-click the Setup.exe file, next you only need to install the default configuration click "Next", by default the installation information will be in the C:\mysql directory.
Next you can enter the "cmd" command in the search box by "start" = "Switch to the C:\mysql\bin directory at the command prompt, and enter the command:

Mysqld.exe--console

If the installation succeeds the above command will output some MySQL startup and InnoDB information.
Verifying MySQL Installation
After the MySQL is successfully installed, some of the underlying tables are initialized, and after the server is started, you can verify that MySQL is working properly with a simple test.
Use the Mysqladmin tool to obtain server status:
Use the mysqladmin command to check the server's version on Linux, where the binaries are located on Linux in/usr/bin on Windows, where the binaries are located in C:\mysql\bin.

[root@host]# mysqladmin--version

On Linux the command will output the following results based on your system information:

Mysqladmin Ver 8.23 distrib 5.0.9-0, for Redhat-linux-gnu on i386

If the above command does not enter any information after execution, your MySQL is not installed successfully.
Execute simple SQL commands with MySQL client (MySQL clients)
You can connect to the MySQL server by using MySQL command on MySQL client (MySQL clients), by default the MySQL server password is empty, so this example does not need to enter a password.
The order is as follows:

[root@host]# MySQL

The above command outputs the mysql> prompt, indicating that you have successfully connected to the MySQL server and that you can execute the SQL command at the mysql> prompt:

Mysql> show DATABASES;

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

MySQL installation after the need to do
after the MySQL installation is successful, the default root user password is blank, and you can create the root password by using the following command:

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

Now you can connect to the MySQL server with the following command:

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

Enter password:*******

Note: When you enter a password, the password is not displayed, you can enter it correctly.
start MySQL when Linux system starts
If you need to start the MySQL server at the start of the Linux system, you need to add the following command to the/etc/rc.local file:

/etc/init.d/mysqld start

Again, you need to add the mysqld binaries to the/etc/init.d/directory.

MySQL Management
Start and close MySQL server
first, we need to check to see if the MySQL server starts with the following command:

Ps-ef | grep mysqld

If MySQL has started, the above command will output the MySQL process list, if MySQL does not start, you can use the following command to start the MySQL server:

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

If you want to shut down the currently running MySQL server, you can execute the following command:

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

Enter Password: Hu Jintao

MySQL User Settings
If you need to add a MySQL user, you only need to add new users to the user table in the MySQL database.
The following is an instance of adding a user named guest with a password of guest123 and authorizing the user to SELECT, INSERT, and update operation rights:

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 users, be careful to use the PASSWORD () function provided by MySQL to encrypt your password. You can see the user password encrypted after the above example: 6f8c114b58f2ce9e.
Note: Note that the FLUSH privileges statement needs to be executed. This command will be loaded back into the authorization table after execution. If you do not use this command, you will not be able to use the newly created user to connect to the MySQL server unless you restart the MySQL server.
You can specify permissions for the user when creating the user, and in the corresponding permission column, set to ' Y ' in the INSERT statement, the user Rights 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 by using the SQL GRANT command, you will add the user Zara to the specified database tutorials 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 above command creates a user information record in the Users table in the MySQL database.
NOTE: MySQL SQL statements are separated by semicolons (;) As an end identity.
/etc/my.cnf File Configuration
in general, you do not need to modify the profile, the default configuration of this file is as follows:

[Mysqld]
Datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir= /var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

In the configuration file, you can specify different error log files stored in the directory, generally you do not need to change these configurations.
commands for managing MySQL
The following lists the commands commonly used during the use of MySQL databases:

• Use database name: Select the MySQL database that you want to manipulate, and all MySQL commands are directed to that database only after the command is used.
• Show DATABASES: Lists the databases for the MySQL database management system.
• Show Tables: Displays all the tables for the specified database, using the use command to select the database to manipulate before using the command.
• show COLUMNS from datasheet: Displays the properties of the datasheet, the type of the property, the primary key information, whether it is NULL, the default value, and other information.
• show the index from data table: Displays detailed index information for the datasheet, including primary key (primary key).
• Show Table STATUS like datasheet \g: This command outputs the performance and statistics of the MySQL database management system.