Mysql database Server configuration method on CentOS _mysql

Source: Internet
Author: User
Tags anonymous install php pack create database
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  
| 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>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&gt;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 ....

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.