centos6.5下安装mysql

1.使用yum命令安装mysql [html] view plaincopy

1. 1. 
      
2. [root@bogon ~]# yum -y install mysql-server
   1. 
      

2.设置开机启动

[html] view plaincopy

1. 1. 
      
2. [root@bogon ~]# chkconfig mysqld on
   1. 
      

3.启动MySQL服务

[html] view plaincopy

1. 1. 
      
2. [root@bogon ~]# service mysqld start
   1. 
      

4.设置MySQL的root用户设置密码

[html] view plaincopy

1. 1. 
      
2. [root@bogon ~]# mysql -u root
3. mysql> select user,host,password from mysql.user;
4. +------+-----------+----------+
5. | user | host | password |
6. +------+-----------+----------+
7. | root | localhost | |
8. | root | bogon | |
9. | root | 127.0.0.1 | |
10. | | localhost | |
11. | | bogon | |
12. +------+-----------+----------+
13. 5 rows in set (0.01 sec)
    1. 
       

查询用户的密码，都为空，用下面的命令设置root的密码为root

[html] view plaincopy

1. 1. 
      
2. mysql> set password for root@localhost=password('root');
3. mysql> exit
   1. 
      

5.用新密码登陆

[html] view plaincopy

1. 1. 
      
2. [root@bogon ~]# mysql -u root -p
3. Enter password:
   1. 
      

6.创建mysql新用户test_user

[html] view plaincopy

1. 1. 
      
2. mysql> create user 'test_user'@'%' identified by 'test_user';
3. Query OK, 0 rows affected (0.00 sec)
   1. 
      

7.给新用户test_user授权，让他可以从外部登陆和本地登陆

注意：@左边是用户名，右边是域名、IP和%，表示可以访问mysql的域名和IP，%表示外部任何地址都能访问。

[html] view plaincopy

1. 1. 
      
2. mysql> grant all privileges on *.* to 'test_user'@'localhost' identified by 'test_user';
3. Query OK, 0 rows affected (0.00 sec)
4. mysql> grant all privileges on *.* to 'test_user'@'%' identified by 'test_user';
5. Query OK, 0 rows affected (0.00 sec)
6. mysql> select user,host,password from mysql.user;
7. +----------+-----------+-------------------------------------------+
8. | user | host | password |
9. +----------+-----------+-------------------------------------------+
10. | root | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
11. | root | bogon | |
12. | root | 127.0.0.1 | |
13. | | localhost | |
14. | | bogon | |
15. | test_user | % | *3046CF87132BBD4FDDF06F321C6859074843B7D3 |
16. | test_user | localhost | *3046CF87132BBD4FDDF06F321C6859074843B7D3 |
17. +----------+-----------+-------------------------------------------+
18. 7 rows in set (0.00 sec)
19. mysql> flush privileges;
20. Query OK, 0 rows affected (0.01 sec)
    1. 
       

8.查看mysql5.1的默认存储引擎

从下面的执行结果可以看出，mysql的默认引擎是MyISAM，这个引擎是不支持事务的。

[html] view plaincopy

1. 1. 
      
2. mysql> show engines;
3. +------------+---------+------------------------------------------------------------+--------------+------+------------+
4. | Engine | Support | Comment | Transactions | XA | Savepoints |
5. +------------+---------+------------------------------------------------------------+--------------+------+------------+
6. | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
7. | CSV | YES | CSV storage engine | NO | NO | NO |
8. | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
9. | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
10. | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
11. +------------+---------+------------------------------------------------------------+--------------+------+------------+
12. 5 rows in set (0.00 sec)
    1. 
       

也可以以下面的方式查看

[html] view plaincopy

1. 1. 
      
2. mysql> show variables like 'storage_engine';
3. +----------------+--------+
4. | Variable_name | Value |
5. +----------------+--------+
6. | storage_engine | MyISAM |
7. +----------------+--------+
8. 1 row in set (0.00 sec)
   1. 
      

9.修改mysql的默认引擎为InnoDB

9.1 停止mysql

[html] view plaincopy

1. 1. 
      
2. mysql> exit;
3. [root@bogon ~]# service mysqld stop
   1. 
      

9.2 修改/etc/my.cnf

[mysqld] 后加入

[html] view plaincopy

1. 1. 
      
2. default-storage-engine=InnoDB
   1. 
      

加入后my.cnf的内容为：

[html] view plaincopy

1. 1. 
      
2. [root@bogon etc]# more my.cnf
3. [mysqld]
4. datadir=/var/lib/mysql
5. socket=/var/lib/mysql/mysql.sock
6. user=mysql
7. # Disabling symbolic-links is recommended to prevent assorted security risks
8. symbolic-links=0
9. 
   
10. default-storage-engine=InnoDB
11. 
    
12. [mysqld_safe]
13. log-error=/var/log/mysqld.log
14. pid-file=/var/run/mysqld/mysqld.pid
    1. 
       

9.3 启动mysql

[html] view plaincopy

1. 1. 
      
2. [root@bogon etc]# service mysqld start
3. Starting mysqld: [ OK ]
   1. 
      

9.4 查看mysql默认存储引擎

[html] view plaincopy

1. 1. 
      
2. [root@bogon etc]# mysql -u root -p
3. Enter password:
4. Welcome to the MySQL monitor. Commands end with ; or \g.
5. Your MySQL connection id is 2
6. Server version: 5.1.73 Source distribution
7. 
   
8. Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
9. 
   
10. Oracle is a registered trademark of Oracle Corporation and/or its
11. affiliates. Other names may be trademarks of their respective
12. owners.
13. 
    
14. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
15. 
    
16. mysql> show variables like 'storage_engine';
17. +----------------+--------+
18. | Variable_name | Value |
19. +----------------+--------+
20. | storage_engine | InnoDB |
21. +----------------+--------+
22. 1 row in set (0.00 sec)
    1. 
       

10.CentOS6.5开放mysql端口3306

CentOS6.5默认是不开放端口的，如果要让外部的系统访问CentOS6.5上的mysql，必须开放mysql的端口3306

10.1 修改/etc/sysconfig/iptables

添加下面一行

[html] view plaincopy

1. 1. 
      
2. -A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
   1. 
      

修改后iptables中的内容是

[html] view plaincopy

1. 1. 
      
2. [root@bogon etc]# more /etc/sysconfig/iptables
3. # Firewall configuration written by system-config-firewall
4. # Manual customization of this file is not recommended.
5. *filter
6. :INPUT ACCEPT [0:0]
7. :FORWARD ACCEPT [0:0]
8. :OUTPUT ACCEPT [0:0]
9. -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
10. -A INPUT -p icmp -j ACCEPT
11. -A INPUT -i lo -j ACCEPT
12. -A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
13. #添加配置项
14. -A INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT
15. -A INPUT -m state --state NEW -m tcp -p tcp --dport 11211 -j ACCEPT
16. -A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
17. -A INPUT -j REJECT --reject-with icmp-host-prohibited
18. -A FORWARD -j REJECT --reject-with icmp-host-prohibited
19. 
    
20. COMMIT
    1. 
       

11.重启防火墙

[html] view plaincopy

1. 1. 
      
2. [root@bogon etc]# service iptables restart
   1. 
      

这样就可以从外部访问mysql了。

至此，mysql在CentOS6.5上的安装过程、用户创建、外部访问的步骤全部完成。