Install and configure MySql-centos

MySql-centos installation configuration (reprinted) CentOS

This article from: http://www.bitsCN.com/database/201305/208114.html

Thanks for the author. if there is any programs with copyright please leave message in my blog,

I will deall with immediately.

Install mysql using yum in centOS Linux

Article 1: install and configure MySQL

Step 1: install MySQL

[Root @ 192 local] # yum-y install mysql-server Plugin install MySQL

[Root @ 192 local] # yum-y install php-mysql plugin install php-mysql

Step 2: Configure MySQL

[Root @ 192 local] # vim/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

Article 2: Start MySQL and set the initial environment

Step 1: Start the MySQL service

[Root @ 192 local] # chkconfig mysqld on startup

[Root @ 192 local] # chkconfig -- list mysqld restart confirm MySQL is started

Mysqld 0: disable 1: disable 2: Enable 3: Enable 4: enable 5: Enable 6: disable

Okay if 2-5 is enabled (or on)

[Root @ 192 local] #/etc/rc. d/init. d/mysqld start slave start MySQL service

Initialize the MySQL database: Installing MySQL system tables...

OK

Filling help tables...

OK

To startmysqld at boot time you have to copy

Support-files/mysql. server to the right place for your system

Please remember to set a password for the MySQL root USER!

To do so, start the server, then issue the following commands:

/Usr/bin/mysqladmin-u root password 'new-password'

/Usr/bin/mysqladmin-u root-h 192.168.0.1 password 'new-password'

Alternatively you can run:

/Usr/bin/mysql_secure_installation

Which will also give you the option of removing the test

Databases and anonymous user created by default. This is

Stronugly recommended for production servers.

See the manual for more instructions.

You can test the MySQL daemon with mysql-test-run.pl

Cd mysql-test; perl mysql-test-run.pl

Please report any problems with the/usr/bin/mysqlbug script!

The latest information about MySQL is available on the web

Http://www.mysql.com

Support MySQL by buying support/licenses athttp: // shop.mysql.com

[OK]

Start mysqld: [OK]

Step 2: set the initial MySQL environment

[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 @ 192 local] # use the root user to log on to the mysql server when the password is not set for MySQL-u root login

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

Your MySQL connection id is 2

Server version: 5.0.95 Source distribution

Copyright (c) 2000,201 1, Oracleand/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help; 'or'/h' for help. type'/C' to clear the current input statement.

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

Mysql> select user, host, password from mysql. user;

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

| User | host | password |

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

| Root | localhost |

| Root | 192.168.0.1 |

| Root | 127.0.0.1 |

| Localhost |

| 192.168.0.1 |

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

5 rows in set (0.03 sec)

Mysql> set password forroot @ localhost = password ('Enter the root password' here); then set the root password

For example, in my system, I set it as follows:

Mysql> set password for root @ localhost = password ('wangyun ');

Query OK, 0 rows affected (0.01 sec)

Mysql> set password for root@192.168.0.1 = password ('wangyun ');

Query OK, 0 rows affected (0.01 sec)

Mysql> set password for root@127.0.0.1 = password ('wangyun ');

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 | 5f2dfe4b07af795b |

| Root | 192.168.0.1 | 5f2dfe4b07af795b |

| Root | 127.0.0.1 | 5f2dfe4b07af795b |

| Localhost |

| 192.168.0.1 |

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

5 rows in set (0.01 sec)

Mysql> exit MySQL server

Bye

[2] test whether the set root password takes effect

[Root @ 192 local] # mysql-u root login log on with the root account with an empty password

ERROR 1045 (28000): Access denied for user 'root' @ 'localhost' (using password: NO)

When this error message appears, the password is successfully set.

[Root @ 192 local] # mysql-u root-p login log on with the root password

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

Server version: 5.0.95 Source distribution

......

Type 'help; 'or'/h' for help. type'/C' to clear the current input statement.

[Root @ 192 local] # mysql-u root-h 127.0.0.1-p login using the password 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 13

Server version: 5.0.95 Source distribution

......

Type 'help; 'or'/h' for help. type'/C' to clear the current input statement.

Mysql> exit MySQL server

Bye

[3] 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 @ 192 local] # mysql-u root-p; logon using the password as root

Enter password: Enter password here

Mysql> select user, host from mysql. user;

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

| User | host |

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

| Root | 127.0.0.1 |

| 192.168.0.1 |

| Root | 192.168.0.1 |

| Localhost |

| Root | localhost |

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

5 rows in set (0.03 sec)

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

Query OK, 2 rows affected (0.04 sec)

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

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

| User | host |

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

| Root | 127.0.0.1 |

| Root | 192.168.0.1 |

| Root | localhost |

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

3 rows in set (0.00 sec)

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

Mysql> exit; quit to log out of the MySQL server

Bye

Article 3: Test

After the settings in the first and second sections, MySQL can be used properly.

Well, the content in article 3 is not necessary!

Let's test MySQL. let's learn or review common SQL statements.

[Root @ 192 local] # mysql-u centospub-p; login using the password as root

Enter password: Enter password here

Mysql> grant all privileges on test. * to centospub @ localhost identified by 'define password here ';

The role creates a user who has full permissions on the test database and is named centospub.

For example, in my system, the settings are as follows:

Mysql> grant all privileges on test. * to centospub @ localhost identified by 'wangyun ';

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 |

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

1 row in set (0.02 sec)

Mysql> exit;

Bye

[Root @ 192 local] # mysql-u centospub-p; login log on to the MySQL server with the newly created centospub user

Enter password: Enter password here

Mysql> create database test; create a database named test

Mysql> show databases; databases

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

| Database |

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

| Information_schema |

| Test |

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

2 rows in set (0.01 sec)

Mysql> use test environment to connect to the database

Database changed

Mysql> create table test

-> (

-> Num int and struct: create a table in the database.

-> Name varchar (50)

-> );

Query OK, 0 rows affected (0.14 sec)

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

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

| Tables_in_test |

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

| Test |

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

1 row in set (0.02 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.14 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql> select * from test; tables

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

| Num | name |

+ ------ + ----------------- + Confirm to be updated to the new value

| 1 | Hello Everyone! |

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

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.01 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)

Confirm that the test database has been deleted (the database named mysql is not visible because it is not the root user)

Mysql> exit MySQL server

Bye

Then, delete the legacy users used for testing.

[Root @ 192 local] # mysql-u root-p;

Enter password:

Mysql> revoke all privileges on *. * from centospub @ localhost;

Revoke cancels the database operation permissions of centospub users

Query OK, 0 rows affected (0.01 sec)

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

← Delete a centospub user

Query OK, 1 row affected (0.14 sec)

Mysql> select user from mysql. user where user = 'centospub ';

Locate the centospub user 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.00 sec)

Mysql> exit;

Bye

[Root @ 192 local] #/etc/rc. d/init. d/httpd restart the HTTP service

Note: The statement is not followed; semicolon

Stop httpd: [OK]

Start httpd: [OK]

Article 4: log location and network server

I. log location

Default log location of mysql, default storage location of mysql log files/var/log/mysqld. log

[Root @ 192 ~] # Cd/var/log

[Root @ 192 log] # pwd

/Var/log

[Root @ 192 log] # ls

Acpid boot. log.2 conman. old dmesg maillog messages.2 ppp samba setroubleshoot tallylog

Anaconda. log boot. log.3 cron faillog maillog.1 messages.3 prelink scrollkeeper. log spooler vbox

Anaconda. syslog boot. log.4 cron.1 tpd maillog.2 messages.4 rpmpkgs secure spooler.1 wtmp

Anaconda. xlog brcm-iscsi.log cron.2 httpd maillog.3 mysqld. log rpmpkgs.1 secure.1 spooler.2 xen

Audit btmp cron.3 lastlog maillog.4 news rpmpkgs.2 secure.2 spooler.3 Xorg.0.log

Boot. log clumond. log cron.4 libvirt messages piranha rpmpkgs.3 secure.3 spooler.4 Xorg.0.log. old

Boot. log.1 conman cups mail messages.1 pm rpmpkgs.4 secure.4 squid yum. log

II. database server

Mysql is a database server that supports the network and often needs to be accessed over the Internet. mysql uses the tcp protocol by default and the port is 3306.

[Root @ 192 log] # cat mysqld. log

130503 11:25:03 mysqld started

InnoDB: The first specified data file./ibdata1 did not exist:

InnoDB: a new database to be created!

130503 11:25:07 InnoDB: Setting file./ibdata1 size to 10 MB

InnoDB: Database physically writes the file full: wait...

130503 11:25:09 InnoDB: Log file./ib_logfile0 did not exist: new to be created

InnoDB: Setting log file./ib_logfile0 size to 5 MB

InnoDB: Database physically writes the file full: wait...

130503 11:25:09 InnoDB: Log file./ib_logfile1 did not exist: new to be created

InnoDB: Setting log file./ib_logfile1 size to 5 MB

InnoDB: Database physically writes the file full: wait...

InnoDB: Doublewrite buffer not found: creating new

InnoDB: Doublewrite buffer created

InnoDB: Creating foreign key constraint system tables

InnoDB: Foreign key constraint system tables created

130503 11:25:12 InnoDB: Started; log sequence number 0 0

130503 11:25:12 [Note]/usr/libexec/mysqld: ready for connections.

Version: '5. 0.95 'socket:'/var/lib/mysql. sock 'port: 3306 Source distribution

[Root @ 192 log] # netstat-tupln

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name

Tcp 0 0 127.0.0.1: 2208 0.0.0.0: * LISTEN 3569/hpiod

Tcp 0 0 0.0.0.0: 5989 0.0.0.0: * LISTEN 3905/cimserver

Tcp 0 0 0.0.0.0: 11111 0.0.0.0: * LISTEN 4783/ricci

Tcp 0 0 0.0.0.0: 3306 0.0.0.0: * LISTEN 3549/mysqld

Tcp 0 0 0.0.0.0: 111 0.0.0.0: * LISTEN 2854/portmap

Tcp 0 0 0.0.0.0: 80 0.0.0.0: * LISTEN 13747/httpd

Tcp 0 0 0.0.0.0: 16851 0.0.0.0: * LISTEN 4669/modclusterd

Tcp 0 0 192.168.122.1: 53 0.0.0.0: * LISTEN 3888/dnsmasq

Tcp 0 0 0.0.0.0: 22 0.0.0.0: * LISTEN 3590/sshd

Tcp 0 0 127.0.0.1: 631 0.0.0.0: * LISTEN 3602/cupsd

Tcp 0 0 0.0.0.0: 952 0.0.0.0: * LISTEN 2890/rpc. statd

Tcp 0 0 127.0.0.1: 25 0.0.0.0: * LISTEN 3643/sendmail: acce

Tcp 0 0 0.0.0.0: 443 0.0.0.0: * LISTEN 13747/httpd

Tcp 0 0 127.0.0.1: 2207 0.0.0.0: * LISTEN 3574/python

Udp 0 0 0.0.0.0: 946 0.0.0.0: * 2890/rpc. statd

Udp 0 0 192.168.122.1: 53 0.0.0.0: * 3888/dnsmasq

Udp 0 0 0.0.0.0: 949 0.0.0.0: * 2890/rpc. statd

Udp 0 0 0.0.0.0: 67 0.0.0.0: * 3888/dnsmasq

Udp 0 0 0.0.0.0: 68 0.0.0.0: * 4516/dhclient

Udp 0 0 0.0.0.0: 5353 0.0.0.0: * 3846/avahi-daemon:

Udp 0 0 0.0.0.0: 111 0.0.0.0: * 2854/portmap

Udp 0 0 0.0.0.0: 631 0.0.0.0: * 3602/cupsd

Udp 0 0 0.0.0.0: 34943 0.0.0.0: * 3846/avahi-daemon: