MySQL database server configuration method on CentOS _ MySQL

How to configure MySQL database server on CentOS

BitsCN.com accidentally found CentOS Professional Forum, remember: http://www.centospub.com

As a result, the initial learning configuration

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 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 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 = utf8

Start 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: off 2: on 3: on 4: on 5: on 6: 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 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 Bye

Test 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 ....

BitsCN.com