mysql安装后配置

1：配置my.cnf

原来的配置

[root@feng02 ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

修改后

[root@feng02 ~]# cat /etc/my.cnf
[client]
password = 123456
port = 3306
default-character-set=utf8
[mysql]
default-character-set = utf8
[mysqld]
port = 3306
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci
lower_case_table_names=1
max_connections=1000
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

2：初始化MySQL

输入：/usr/bin/mysql_install_db

[root@feng02 ~]# /usr/bin/mysql_install_db
Installing MySQL system tables...
150905 13:51:04 [Warning] /usr/libexec/mysqld: ignoring option '--character-set-client-handshake' due to invalid value 'utf8'
OK
Filling help tables...
150905 13:51:04 [Warning] /usr/libexec/mysqld: ignoring option '--character-set-client-handshake' due to invalid value 'utf8'
OK

To start mysqld 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 feng02 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
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

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

3：启动Mysql

[root@feng02 ~]# service mysqld start
Starting mysqld:  [  OK  ]

4：连接Mysql

输入：mysql -uroot -p

直接回车，不用输入密码

[root@feng02 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/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> update user set password=password('123456') where user='root';
ERROR 1046 (3D000): No database selected
mysql> show database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

设置root的密码：

输入：use mysql;

输入：update user set password=password('123456') where user='root';

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set password=password('123456') where user='root';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> exit
Bye

5：重启mysql

输入：service mysqld restart

[root@feng02 ~]# service mysqld restart

Stopping mysqld:  [  OK  ]
Starting mysqld:  [  OK  ]

6：使用密码登陆：

[root@feng02 ~]# mysql -uroot -p123456
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/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> 

7：设置允许远程登录

输入：use mysql;
输入：select host,user,password from user;
输入：update user set host='%' where user='root' and host='localhost';
输入：flush privileges;
输入： exit;

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select host,user,password from user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| feng02    | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost |      |                                           |
| feng02    |      |                                           |
+-----------+------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql> update user set host='%' where user='root' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

mysql> exit;
Bye

8：连接需要输入IP或主机名了

[root@feng02 ~]# mysql -uroot -p123456
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@feng02 ~]# mysql -hfeng02 -uroot -p123456
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.73 Source distribution