Mysql database Server configuration method on CentOS _mysql

Inadvertently also found CentOS professional forum, remember: http://www.centospub.com

So, the beginning of the learning configuration

This environment:

CentOS System,

Mysql-server (version forgotten, because it was installed using Yum)

Process as follows (reprint, according to my installation detailed requirements, slightly modified):

When we refer to the website architecture, we often see "LAMP", which is "Linux + Apache + MySQL + PHP". Mysql+php's collaborative success story has been a lot. For example, foreign PHPBB,VBB, the domestic discuz! forum and so on, are all implemented with PHP + MySQL. Compared with those business-specific database, the function and size of MySQL may be less than many people say, but for us personally, its function is considered complete. Also, the number of people online at the discuz! official forum can prove MySQL's stability.

Here, introduce the CentOS MySQL database server construction method.

Installing MySQL and related components

First, install MySQL.

[Root@sample ~]#Yum-y Install Mysql-server 　 ← Install MySQL Setting up Install Process Setting up repositories Dag 100% |=========================| 1.1 KB 00:00 Update 100% |=========================| 951 B 00:00 Base 100% |=========================| 1.1 KB 00:00 Addons 100% |=========================| 951 B 00:00 Extras 100% |=========================| 1.1 KB 00:00 Reading repository metadata in from local files primary.xml.gz 100% |=========================| All KB 00:00 Update: ################################################## 261/261 Added 5 new packages, deleted 0 old in 2.02 seconds Reducing Dag RPM Repository for Red Hat Enterprise Linux-included 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% |=========================| MB 00:00 ---> Package mysql-server.i386 0:4.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 00:00 ---> Package perl-dbd-mysql.i386 0:2.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% |=========================| KB 00:00 ---> Package mysql.i386 0:4.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% |=========================| One KB 00:00 ---> Package perl-dbi.i386 0:1.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 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% |=========================| kB 00:00 (2/4): mysql-server-4.1.2 100% |=========================| 9.8 mb 00:11 (3/4): Mysql-4.1.20-1.rhe 100% |=========================| 2.9 MB 00:03 (4/4): Perl-dbi-1.40-8.i3 100 % |=========================| 466 KB 00:01 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 0:4.1.20-1.rhel4.1 Dependency Installed:mysql.i386 0:4.1.20-1.rhel4.1 perl-dbd-mysql.i386 0:2.9004-3.1 perl-dbi.i386 complete!

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

[Root@sample ~]#Yum-y Install Php-mysql 　 ← Install Php-mysql Setting up Install Process Setting up repositories Reading repository metadata in from local files Reducing Dag RPM Repository for Red Hat Enterprise Linux-included 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% |=========================| KB 00:00 ---> Package php-mysql.i386 0:4.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 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% |=========================| KB 00:00 Running Transaction Test Finished Transaction Test Transaction Test Succeeded Running Transaction Installing:php-mysql ######################### [1/1] installed:php-mysql.i386 0:4.3.9-3.15 complete!

Configure MySQL

Then, configure MySQL.

[root@sample ~]#& nbsp vi/etc/my.cnf  ← edit MySQL's configuration file [mysqld] Datadir=/var/lib/mysql Socket=/var/lib/mysql/mysql.sock # Default to using old Password format for compatibility with MySQL 3.x # clients (those using the Mysqlclient10 compatibility). Old_passwords=1  ← to find this line, add a new rule below this line, and let MySQL's default encoding be UTF-8 default-character-set = UTF8  ← Add this line [mysql] Default-character-set = UTF8

Start the MySQL service

Start MySQL and have MySQL start automatically with the system after the system restarts.

[Root@sample ~]#Chkconfig mysqld on 　 ← set the MySQL service to boot from the system start [root@sample ~]#  ← confirm MySQL from start mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off ← if 2--5 is on, OK [root@sample ~]#   ← start the MySQL service Initializing MySQL database: [ ok ] Starting MySQL: [ ok  ]

MySQL Initial environment setting

[1] Set the password for the MySQL root user

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

[Root@sample ~]#Mysql-u Root 　 ← Login to MySQL server with root user 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;  ← View user information +------+----------------------- -------+---------------+ | user | host | password | 　　　　　　 +------+------------------------------+---------------+ | root | localhost |　|  ←root password is empty &NBSP; | Root |　　　　　　 sample.centospub.com |　|　　　 ←root password is empty | | 　　　　　　sample.centospub.com | |　　　 | | 　　　　　　localhost | | +------+------------------------------+---------------+ 4 rows in Set (0.00 sec) mysql> Set password for Root@localhost=password (' Fill in root password ' here ); ← Set the root password Query OK, 0 rows affected (0.01 sec) mysql> Set password for root@ ' sample.centospub.com ' =password (' Fill in root password ' here); ← Set the root password Query OK, 0 rows affected (0.01 sec) Mysql&gt;Select User,host,password from Mysql.user; 　 ← View user information +------+--------------------------------+---------------- ----------+ | user | host | password | +------+--------------------------------+--------------------------+ | root | localhost | 19b68057189b027f |  ←root password is set | root | sample.centospub.com |< Span class= "Apple" > 19b68057189b027f | 　　 Span class= "Apple" > ←root password is set | | 　　　　　　　　　sample.centospub.com | | 　　 | |　　　　　　　　　 localhost | | +------+--------------------------------+--------------------------+ 4 rows in Set (0.01 sec) Mysql> exit ← Quit MySQL Server Bye

Then, test to see if the root password is in effect.

[Root@sample ~]#Mysql-u Root 　 ← Login with root via empty password ERROR 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using Password:no)← This error message indicates that the password setting was successful [Root@localhost ~]#Mysql-u root-h sample.centospub.com 　 ← Login with root via empty password ERROR 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using Password:no) ← This error message indicates that the password setting was successful [Root@sample ~]#Mysql-u root-p 　 ← Login with root via password Enter Password:← Enter the password here Welcome to the MySQL Monitor. Commands End With; or \g.← Confirm password to log in successfully 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 with root via password Enter Password: ← Enter the password here Welcome to the MySQL Monitor. Commands End With; or \g. ← Confirms that you can successfully log in with a 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 ← Quit MySQL Server Bye

[2] Delete anonymous users

After MySQL has just been installed, there are users with a user name and a blank password. This allows the database server to have the possibility of being logged on without a password. To eliminate the pitfalls, delete the anonymous user.

[Root@sample ~]#Mysql-u root-p 　 ← Login with root via password Enter Password:← Enter the 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; ← 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= '; ← Delete anonymous Users Query OK, 2 rows affected (0.17 sec) mysql> Select User,host from Mysql.user; ← View user Information +------+----------------------------+ | user | Host | +------+----------------------------+ | Root | localhost | | Root | sample.centospub.com | +------+----------------------------+ 2 rows in Set (0.00 sec) mysql> exit ← Quit MySQL Server Bye

[3] Delete test database

After MySQL is installed, there is an empty database named Test, which is deleted. Note here that the system defaults to a database named MySQL, which is used for system management, so please do not delete.

[Root@sample ~]#Mysql-u root-p 　 ← Login with root via password Enter Password:← Enter the 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; ← View the database that the system already exists +-------------+ | Database | +-------------+ | MySQL | | Test | +------------+ 2 rows in Set (0.02 sec) mysql> drop database test; ← Delete an empty database named Test Query OK, 0 rows affected (0.07 sec) Mysql> show databases; ← View the database that the system already exists +-------------+ | Database | +-------------+ |　MySQL | ← Confirms that the database named Test is deleted and no longer exists +-------------+ 1 row in Set (0.00 sec) Mysql>Exit ← Quit MySQL Server Bye

Test MySQL

The following is a test of MySQL. This includes setting up new users and trying to build database and data tables with instructions for database operations on relational databases. Here, the new user takes centospub as an example.

[Root@sample ~]# Mysql-u root-p 　 ← Login with root via password Enter Password:← Enter the 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 in test.* to centospub@localhost identified by ' Define password here '; ← Create a user named Centospub that has full operational rights to the test database Query OK, 0 rows affected (0.03 sec) mysql> select user from Mysql.user where user=　' Centospub ';  ← confirm the presence of centospub users +---------+ | user | +---------+ |　 centospub |  ← confirmed that Centospub has been established +---------+ 1 row in Set (0.01 sec) Mysql> exit ← Quit MySQL Server Bye [Root@sample ~]# mysql-u centospub-p ← Login to the MySQL server with the newly established centospub user Enter Password: ← Enter the password here Welcome to the MySQL Monitor. Commands End With; or \g. Your MySQL Connection ID is-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; ← View the database that the system already exists +-------------+ | 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)); ← Create a table in the database Query OK, 0 rows affected (0.03 sec) Mysql> show tables; ← View tables that already exist in the database +-------------------+ | Tables_in_test | +-------------------+ | Test | +-------------------+ 1 row in Set (0.01 sec) mysql> INSERT INTO test values (1, ' Hello world! '); ← Insert a value into the table Query OK, 1 row affected (0.02 sec) mysql> SELECT * from test; ← View information for tables in the database +------+-------------------+ | num | name | +------+-------------------+ | 1 | 　 Hello world! | +------+-------------------+ 1 row in Set (0.00 sec) mysql> Update test set name= ' Hello everyone! '; ← Update the information of the table, give the new value Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0 mysql> SELECT * from test; ← View information for tables in the database +------+----------------------+ | num | name | +------+----------------------+ | 1 |　 Hello everyone! | ← Confirm being updated to the new value +------+----------------------+ 1 row in Set (0.01 sec) mysql> Delete from test where num=1; ← Delete the values in the table Query OK, 1 row Affected (0.00 sec) mysql> SELECT * from test; ← Confirm deletion result Empty Set (0.01 sec) mysql> drop table test; ← Delete Table Query OK, 0 rows affected (0.01 sec) Mysql> show tables; ← View table Information Empty Set (0.00 sec) ← Confirm that the table has been deleted mysql> drop database test; ← Delete a database named test Query OK, 0 rows affected (0.01 sec) Mysql> show databases; ← View a database that already exists Empty Set (0.01 sec) ← Confirms that the test database has been deleted (this is not a root user relationship and cannot see a database named MySQL) mysql> exit ← Quit MySQL Server Bye

Then, delete the test-used legacy user.

[Root@sample ~]#Mysql-u root-p 　 ← Login with root via password Enter Password:← Enter the password here Welcome to the MySQL Monitor. Commands End With; or \g. Your MySQL Connection ID is the to server version:4.1.20 Type ' help, ' or ' \h ' for help. Type ' \c ' to clear the buffer. mysql> Revoke all privileges to *.* from Centospub@localhost; ← Cancels centospub user to the database operation permission Query OK, 0 rows Affected (0.00 sec) mysql> Delete from mysql.user where user= ' centospub ' and host= ' localhost '; ← Delete centospub User Query OK, 1 row affected (0.01 sec) mysql> Select User from Mysql.user where user= ' centospub '; ← Find user centospub, confirm deleted or not Empty Set (0.01 sec) ← Confirm centospub User no longer exists mysql> flush privileges; ← Refresh, make the above operation effective Query OK, 0 rows affected (0.01 sec) mysql> exit Bye

Finally, restart the HTTP service so that Php-mysql is reflected in the HTTP service.

[Root@sample ~]# /etc/rc.d/init.d/httpd restart ← Restart the HTTP service stopping httpd: [ OK ] Starting httpd: [ OK ]

Finally get it!

(*^__^*) Hee ..., then configure phpMyAdmin ....