How to install and configure MySQL in CentOS

1. Install MySQL in CentOS [root @ sample ~] # Yum-yinstallmysql-serverlarr; install MySQL and then install the PHP tool ld for accessing the MySQL database

1. Install MySQL in CentOS [root @ sample ~] # Yum-y install mysql-server larr; install MySQL and then install the PHP tool ld for accessing the MySQL database

1. Install MySQL in CentOS

[Root @ sample ~] # Yum-y install mysql-server Plugin install MySQL

Then, install the PHP tool "php-MySQL" to access the mysql database"

[Root @ sample ~] # Yum-y install php-mysql plugin install php-mysql

2. Configure MySQL

[Root @ sample ~] # Vi/etc/my. cnf plugin edit the MySQL configuration file

# Locate here

[Client]
# Password = your_password
Port = 3306
Socket =/tmp/mysql. sock
Default-character-set = utf8

[Mysqld]

Datadir =/var/lib/mysql

Socket =/var/lib/mysql. sock

# Default to using old password format for compatibility with mysql 3.x

# Clients (those using the mysqlclient10 compatibility package ).

Old_passwords = 1 locate find this line, add new rules under this line, let MySQL Default Encoding As UTF-8 (if not found, add in the end of this file)

Default-character-set = utf8 character add this line

Start the MySQL service and Run status in MySQL to check whether utf8 is all supported and enable MySQL to automatically start with the system after the system restarts.

[Root @ sample ~] # Chkconfig mysqld on startup

[Root @ sample ~] # Chkconfig -- list mysqld restart confirm that MySQL is started automatically

Mysqld 0: off 1: off 2: on 3: on 4: on 5: on 6: off okay if 2--5 is on

[Root @ sample ~] # Service mysqld start slave start MySQL service

3. Set the root user password and Related Optimization Configuration

When MySQL was just installed, its root user was not set a password. First, set the root password of MySQL.

[Root @ sample ~] # Mysql-u root login use the root user to log on to the MySQL server

Welcome to the MySQL monitor. Commands end with; or \ g.

Your MySQL connection id is 2 to server version: 4.1.20

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Mysql> select user, host, password from mysql. user; login view user information

+ ------ + ------------------------------ + --------------- +

| User | host | password |

+ ------ + ------------------------------ + --------------- +

| Root | localhost | the guest root password is blank.

| Root | sample.centospub.com | the guest root password is blank.

| Sample.centospub.com |

| Localhost |

+ ------ + ------------------------------ + --------------- +

4 rows in set (0.00 sec)

Mysql> set password for root @ localhost = password ('yourpwd'); configure the root password

Query OK, 0 rows affected (0.01 sec)

Mysql> set password for root @ 'sample .centospub.com '= password ('newid'); configure the root password

Query OK, 0 rows affected (0.01 sec)

Mysql> select user, host, password from mysql. user; login view user information

+ ------ + -------------------------------- + -------------------------- +

| User | host | password |

+ ------ + -------------------------------- + -------------------------- +

| Root | localhost | 19b68057189b027f | the guest root password is set and encrypted.

| Root | sample.centospub.com | 19b68057189b027f | the guest root password is set and encrypted.

| Sample.centospub.com |

| Localhost |

+ ------ + -------------------------------- + -------------------------- +

4 rows in set (0.01 sec)

Mysql> exit MySQL Server

Bye

Then, test whether the root password has taken effect.

[Root @ sample ~] # Mysql-u root login log on with a blank password

ERROR 1045 (28000): Access denied for user 'root' @ 'localhost' (using password: NO) indicates that the password is successfully set.

[Root @ localhost ~] # Mysql-u root-h sample.centospub.com login log on with root using an empty password

ERROR 1045 (28000): Access denied for user 'root' @ 'localhost' (using password: NO) indicates that the password is successfully set.

[Root @ sample ~] # Mysql-u root-p login using the password to log on as root

Enter password: Enter password here

Welcome to the MySQL monitor. Commands end with; or \ g. login confirm that you can log on with the password

Your MySQL connection id is 5 to server version: 4.1.20

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Mysql> exit

[Root @ sample ~] # Mysql-u root-h sample.centospub.com-p login Login login using the password root

Enter password: Enter password here

Welcome to the MySQL monitor. Commands end with; or \ g. login confirm that you can log on with the password

Your MySQL connection id is 6 to server version: 4.1.20

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Mysql> exit MySQL Server

Bye

Delete Anonymous Users

After MySQL is installed, a user with a blank username and password exists. This makes it possible for the database server to be logged on without a password. To eliminate potential risks, Remove anonymous users.

[Root @ sample ~] # Mysql-u root-p login using the password to log on as root

Enter password: Enter password here

Welcome to the MySQL monitor. Commands end with; or \ g.

Your MySQL connection id is 7 to server version: 4.1.20

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Mysql> select user, host from mysql. user; users view user information

+ ------ + ---------------------------- +

| User | host |

+ ------ + ---------------------------- +

| Localhost |

| 127.0.0.1 |

| Root | localhost |

| Sample.centospub.com |

| Root | sample.centospub.com |

+ ------ + ---------------------------- +

4 rows in set (0.02 sec)

Mysql> delete from mysql. user where user = ''; anonymous deletes Anonymous Users

Query OK, 2 rows affected (0.17 sec)

Mysql> select user, host from mysql. user; users view user information

+ ------ + ---------------------------- +

| User | host |

+ ------ + ---------------------------- +

| Root | localhost |

| Root | sample.centospub.com |

+ ------ + ---------------------------- +

2 rows in set (0.00 sec)

Mysql> exit MySQL Server

Delete Test Database

After MySQL is installed, an empty database named test exists. Delete it. Note that a database named mysql is used for system management by default, so do not delete it.

[Root @ sample ~] # Mysql-u root-p login using the password to log on as root

Enter password: Enter password here

Welcome to the MySQL monitor. Commands end with; or \ g.

Your MySQL connection id is 8 to server version: 4.1.20

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Mysql> show databases; databases

+ ------------- +

| Database |

+ ------------- +

| Mysql |

| Test |

+ ------------ +

2 rows in set (0.02 sec)

Mysql> drop database test; empty Delete empty database named test

Query OK, 0 rows affected (0.07 sec)

Mysql> show databases; databases

+ ------------- +

| Database |

+ ------------- +

| Mysql | confirm that the database named test has been deleted and does not exist.

+ ------------- +

1 row in set (0.00 sec)

Mysql> exit MySQL Server

4. Test MySQL

MySQL is tested. This includes creating a new user and using commands for database operations on relational databases to create databases and data tables. Here, the new user uses centospub as an example.

[Root @ sample ~] # Mysql-u root-p login using the password to log on as root

Enter password: Enter password here

Welcome to the MySQL monitor. Commands end with; or \ g.

Your MySQL connection id is 9 to server version: 4.1.20

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Mysql> grant all privileges on test. * to centospub @ localhost identified by 'newpwd'; Revoke creates a user named centospub who has full operation permissions on the test database.

Query OK, 0 rows affected (0.03 sec)

Mysql> select user from mysql. user where user = 'centospub '; confirm whether the centospub user exists.

+ --------- +

| User |

+ --------- +

| Centospub | confirm that centospub has been created

+ --------- +

1 row in set (0.01 sec)

Mysql> exit MySQL Server

[Root @ sample ~] # Mysql-u centospub-p login log on to the MySQL server with the newly created centospub user

Enter password: Enter password here

Welcome to the MySQL monitor. Commands end with; or \ g.

Your MySQL connection id is 10 to server version: 4.1.20

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Mysql> create database test; create a database named test

Query OK, 1 row affected (0.00 sec)

Mysql> show databases; databases

+ ------------- +

| Database |

+ ------------- +

| Test |

+ ------------- +

1 row in set (0.00 sec)

Mysql> use test; connect to the database

Database changed

Mysql> create table test (num int, name varchar (50); Tables create tables in the database

Query OK, 0 rows affected (0.03 sec)

Mysql> show tables; tables view existing tables in the database

+ ------------------- +

| Tables_in_test |

+ ------------------- +

| Test |

+ ------------------- +

1 row in set (0.01 sec)

Mysql> insert into test values (1, 'Hello World! '); Inserts a value into the table.

Query OK, 1 row affected (0.02 sec)

Mysql> select * from test; Tables

+ ------ + ------------------- +

| Num | name |

+ ------ + ------------------- +

| 1 | Hello World! |

+ ------ + ------------------- +

1 row in set (0.00 sec)

Mysql> update test set; values: update table information and assign new values.

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql> select * from test; Tables

+ ------ + ---------------------- +

| Num | name |

+ ------ + ---------------------- +

| 1 | Hello Everyone! | Confirm to be updated to the new value

+ ------ + ---------------------- +

1 row in set (0.01 sec)

Mysql> delete from test where num = 1; values: delete the values in the table.

Query OK, 1 row affected (0.00 sec)

Mysql> select * from test; confirm the deletion result

Empty set (0.01 sec)

Mysql> drop table test; Tables Delete tables

Query OK, 0 rows affected (0.01 sec)

Mysql> show tables; tables

Empty set (0.00 sec) Validation table deleted

Mysql> drop database test; Revoke Delete the database named test

Query OK, 0 rows affected (0.01 sec)

Mysql> show databases; databases

Empty set (0.01 sec) Verify that the test database has been deleted (this is a non-root user relationship, and the database named mysql is not visible)

Mysql> exit MySQL Server

Bye

Then, delete the legacy users used for testing.

[Root @ sample ~] # Mysql-u root-p login using the password to log on as root

Enter password: Enter password here

Welcome to the MySQL monitor. Commands end with; or \ g.

Your MySQL connection id is 12 to server version: 4.1.20

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Mysql> revoke all privileges on *. * from centospub @ localhost; revoke cancels the database operation permissions of centospub users.

Query OK, 0 rows affected (0.00 sec)

Mysql> delete from mysql. user where user = 'centospub 'and host = 'localhost'; users delete centospub users

Query OK, 1 row affected (0.01 sec)

Mysql> select user from mysql. user where user = 'centospub '; locate the user centospub and check whether the user has been deleted.

Empty set (0.01 sec) validate that the centospub user does not exist

Mysql> flush privileges; volume refresh to make the above operations take effect

Query OK, 0 rows affected (0.01 sec)

Mysql> exit

Bye

5. Restart the HTTP service once, so that php-mysql is reflected in the HTTP service.

[Root @ sample ~] #/Etc/rc. d/init. d/httpd restart the HTTP service

Stopping httpd: [OK]

Starting httpd: [OK]