How to configure MySQL database server on CentOS

How to configure MySQL database server on CentOS

This environment:

CentOS system,

Mysql-server (version forgot, because it was installed using yum)

The process is as follows (reprinted, according to detailed installation requirements, slightly add modifications ):

When talking about the website architecture, we often see the saying "LAMP", which is "Linux + Apache + MySQL + PHP ". There have been many successful cases of MySQL + PHP collaboration. For example, PHPBB, VBB, domestic Discuz! Forums and other programs are implemented using PHP + MySQL. Compared with dedicated commercial databases, MySQL functions and scale may be inferior to those described by many people, but for us, its functions are complete. Besides, Discuz! The number of concurrent online users on the official forum can also prove the stability of MySQL.

Here, we will introduce how to build a MySQL database server in CentOS.

Install MySQL and related components

First, install MySQL.

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

Setting up Install Process
Setting up repositories
Dag 100% | =================================| 1.1 kB
Update 100% | =================================| 951 B
Base 100% | =================================| 1.1 kB
Addons 100% | =============================| 951 B
Extras 100% | =============================| 1.1 kB
Reading repository metadata in from local files
Primary.xml.gz 100% | =======================| 106 kB
Update: ######################################## ######## 261/261
Added 5 new packages, deleted 0 old in 2.02 seconds
Cing Dag RPM Repository for Red Hat Enterprise Linux to supported ded packages only
Finished
Parsing package install arguments
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for mysql-server to pack into transaction set.
Mysql-server-4.1.20-1.RHE 100% | =================================| 29 kB
---> Package mysql-server.i386. 1.20-1. RHEL4.1 set to be updated
--> Running transaction check
--> Processing Dependency: libmysqlclient. so.14 (libmysqlclient_14) for package: mysql-server
--> Processing Dependency: perl-DBD-MySQL for package: mysql-server
--> Processing Dependency: perl-DBI for package: mysql-server
--> Processing Dependency: perl (DBI) for package: mysql-server
--> Processing Dependency: mysql = 4.1.20-1. RHEL4.1 for package: mysql-server
--> Processing Dependency: libmysqlclient. so.14 for package: mysql-server
--> Processing Dependency: libmysqlclient_r.so.14 (libmysqlclient_14) for package: mysql-server
--> Processing Dependency: libmysqlclient_r.so.14 for package: mysql-server
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for perl-DBD-MySQL to pack into transaction set.
Perl-DBD-MySQL-2.9004-3.1 100% | =================================| 5.4 kB
---> Package perl-DBD-MySQL.i386. 9004-3.1 set to be updated
---> Downloading header for mysql to pack into transaction set.
Mysql-4.1.20-1.RHEL4.1.i3 100% | =================================| 35 kB
---> Package mysql. i386. 1.20-1. RHEL4.1 set to be updated
---> Downloading header for perl-DBI to pack into transaction set.
Perl-DBI-1.40-8.i386.rpm 100% | =================================| 11 kB
---> Package perl-DBI.i386. 40-8 set to be updated
--> Running transaction check

Dependencies Resolved

========================================================== ============================================
Package Arch Version Repository Size
========================================================== ============================================
Installing:
Mysql-server i386 4.1.20-1. RHEL4.1 update 9.8 M
Installing for dependencies:
Mysql i386 4.1.20-1. RHEL4.1 update 2.9 M
Perl-DBD-MySQL i386 2.9004-3.1 base 111 k
Perl-DBI i386 1.40-8 base 466 k

Transaction Summary
========================================================== ============================================
Install 4 Package (s)
Update 0 Package (s)
Remove 0 Package (s)
Total download size: 13 M
Downloading Packages:
(1/4): perl-DBD-MySQL-2.9 100% | =========================| 111 kB
(2/4): mysql-server-4.1.2 100% | =========================| 9.8 MB
(3/4): mysql-4.1.20-1.RHE 100% | =========================| 2.9 MB
(4/4): perl-DBI-1.40-8.i3 100% |=========================| 466 kB
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing: perl-DBI ######################### [1/4]
Installing: mysql ####################### [2/4]
Installing: perl-DBD-MySQL ######################### [3/4]
Installing: mysql-server ######################## [4/4]

Installed: mysql-server.i386. 1.20-1. RHEL4.1
Dependency Installed: mysql. i386. 1.20-1. RHEL4.1 perl-DBD-MySQL.i386. 9004-3.1 perl-DBI.i386. 40-8
Complete!

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

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

Setting up Install Process
Setting up repositories
Reading repository metadata in from local files
Cing Dag RPM Repository for Red Hat Enterprise Linux to supported ded packages only
Finished
Parsing package install arguments
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for php-mysql to pack into transaction set.
Php-mysql-4.3.9-3.15.i386 100% | =================================| 18 kB
---> Package php-mysql.i386. 3.9-3.15 set to be updated
--> Running transaction check

Dependencies Resolved

========================================================== ============================================
Package Arch Version Repository Size
========================================================== ============================================
Installing:
Php-mysql i386 4.3.9-3.15 update 35 k

Transaction Summary
========================================================== ============================================
Install 1 Package (s)
Update 0 Package (s)
Remove 0 Package (s)
Total download size: 35 k
Downloading Packages:
(1/1): php-mysql-4.3.9-3. 100% | = 35 kB
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing: php-mysql ######################## [1/1]

Installed: php-mysql.i386. 3.9-3.15
Complete!Configure MySQL

Then, configure MySQL.

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

[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 encode find this line, add a new rule below this line, let MySQL's default code as UTF-8
Default-character-set = utf8 character add this line

Add the following statement at the end of the configuration file:

[Mysql]
Default-character-set = utf8Start MySQL Service

Start MySQL 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: off2: on 3: on 4: on 5: on6: off okay if 2--5 is on

[Root @ sample ~] #/Etc/rc. d/init. d/mysqld start slave start MySQL Service

Initializing MySQL database: [OK]
Starting MySQL: [OK]MySQL initial Environment Settings

[1] set a password for the root user of MySQL

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

[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 ('Enter the root password' here); then set the root password
Query OK, 0 rows affected (0.01 sec)

Mysql> set password for root @ 'sample .centospub.com '= password ('Enter the root password' here); 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.
| Root | sample.centospub.com | 19b68057189b027f | the ← root password is set.
| 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
Bye

[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

[2] deleting 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. Delete anonymous users to eliminate potential risks.

[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
Bye

[3] deleting a 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
ByeTest MySQL

MySQL is tested. This includes creating new users and using commands for database operations on relational databases to try 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 'define the password' here; principal 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
Bye

[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 environment to 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 name = 'Hello Everyone! '; Then, update the table information and assign a new value.
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

Finally, 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]

Finally!

(* ^__ ^ *) Xi ......, Next, configure phpmyadmin ....