在Linux(CentOS 6.2 64位操作系统)下安装MySQL数据并开启远程访问权限

WorkLifeRecords 2021-06-24

589

具体步骤如下：

卸载系统自带的MySQL操作如下

[root@localhost ~]# rpm -qa|grep -i mysql  
mysql-libs-5.1.52-1.el6_0.1.x86_64  
[root@localhost ~]# rpm -e --nodeps mysql-libs-5.1.52-1.el6_0.1.x86_64
[root@localhost ~]# whereis mysql
mysql: /usr/lib64/mysql /usr/share/mysql
[root@localhost ~]# rm -rf /usr/lib64/mysql
[root@localhost ~]# rm -rf /usr/share/mysql

使用rpm命令安装MySQL数据库服务端

[root@localhost app]# rpm -ivh MySQL-server-5.6.21-1.linux_glibc2.5.x86_64.rpm   
Preparing...                ########################################### [100%]  
   1:MySQL-server           ########################################### [100%]  
warning: user mysql does not exist - using root  
warning: group mysql does not exist - using root  
2015-03-14 01:31:44 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).  
2015-03-14 01:31:45 3329 [Note] InnoDB: Using atomics to ref count buffer pool pages  
2015-03-14 01:31:45 3329 [Note] InnoDB: The InnoDB memory heap is disabled  
2015-03-14 01:31:45 3329 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins  
2015-03-14 01:31:45 3329 [Note] InnoDB: Memory barrier is not used  
2015-03-14 01:31:45 3329 [Note] InnoDB: Compressed tables use zlib 1.2.3  
2015-03-14 01:31:45 3329 [Note] InnoDB: Using Linux native AIO  
2015-03-14 01:31:45 3329 [Note] InnoDB: Using CPU crc32 instructions  
2015-03-14 01:31:47 3329 [Note] InnoDB: Initializing buffer pool, size = 128.0M  
2015-03-14 01:31:48 3329 [Note] InnoDB: Completed initialization of buffer pool  
2015-03-14 01:31:48 3329 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!  
2015-03-14 01:31:48 3329 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB  
2015-03-14 01:31:48 3329 [Note] InnoDB: Database physically writes the file full: wait...  
2015-03-14 01:31:48 3329 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB  
2015-03-14 01:31:54 3329 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB  
2015-03-14 01:32:03 3329 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0  
2015-03-14 01:32:03 3329 [Warning] InnoDB: New log files created, LSN=45781  
2015-03-14 01:32:03 3329 [Note] InnoDB: Doublewrite buffer not found: creating new  
2015-03-14 01:32:03 3329 [Note] InnoDB: Doublewrite buffer created  
2015-03-14 01:32:03 3329 [Note] InnoDB: 128 rollback segment(s) are active.  
2015-03-14 01:32:03 3329 [Warning] InnoDB: Creating foreign key constraint system tables.  
2015-03-14 01:32:03 3329 [Note] InnoDB: Foreign key constraint system tables created  
2015-03-14 01:32:03 3329 [Note] InnoDB: Creating tablespace and datafile system tables.  
2015-03-14 01:32:03 3329 [Note] InnoDB: Tablespace and datafile system tables created.  
2015-03-14 01:32:03 3329 [Note] InnoDB: Waiting for purge to start  
2015-03-14 01:32:03 3329 [Note] InnoDB: 5.6.21 started; log sequence number 0  
A random root password has been set. You will find it in '/root/.mysql_secret'.  
2015-03-14 01:32:04 3329 [Note] Binlog end  
2015-03-14 01:32:04 3329 [Note] InnoDB: FTS optimize thread exiting.  
2015-03-14 01:32:04 3329 [Note] InnoDB: Starting shutdown...  
2015-03-14 01:32:06 3329 [Note] InnoDB: Shutdown completed; log sequence number 1625977  
  
  
2015-03-14 01:32:06 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).  
2015-03-14 01:32:06 3353 [Note] InnoDB: Using atomics to ref count buffer pool pages  
2015-03-14 01:32:06 3353 [Note] InnoDB: The InnoDB memory heap is disabled  
2015-03-14 01:32:06 3353 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins  
2015-03-14 01:32:06 3353 [Note] InnoDB: Memory barrier is not used  
2015-03-14 01:32:06 3353 [Note] InnoDB: Compressed tables use zlib 1.2.3  
2015-03-14 01:32:06 3353 [Note] InnoDB: Using Linux native AIO  
2015-03-14 01:32:06 3353 [Note] InnoDB: Using CPU crc32 instructions  
2015-03-14 01:32:06 3353 [Note] InnoDB: Initializing buffer pool, size = 128.0M  
2015-03-14 01:32:06 3353 [Note] InnoDB: Completed initialization of buffer pool  
2015-03-14 01:32:06 3353 [Note] InnoDB: Highest supported file format is Barracuda.  
2015-03-14 01:32:06 3353 [Note] InnoDB: 128 rollback segment(s) are active.  
2015-03-14 01:32:06 3353 [Note] InnoDB: Waiting for purge to start  
2015-03-14 01:32:06 3353 [Note] InnoDB: 5.6.21 started; log sequence number 1625977  
2015-03-14 01:32:06 3353 [Note] Binlog end  
2015-03-14 01:32:06 3353 [Note] InnoDB: FTS optimize thread exiting.  
2015-03-14 01:32:06 3353 [Note] InnoDB: Starting shutdown...  
2015-03-14 01:32:08 3353 [Note] InnoDB: Shutdown completed; log sequence number 1625987  
  
  
  
  
A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !  
You will find that password in '/root/.mysql_secret'.  
  
You must change that password on your first connect,  
no other statement but 'SET PASSWORD' will be accepted.  
See the manual for the semantics of the 'password expired' flag.  
  
Also, the account for the anonymous user has been removed.  
  
In addition, you can run:  
  
  /usr/bin/mysql_secure_installation  
  
which will also give you the option of removing the test database.  
This is strongly recommended for production servers.  
  
See the manual for more instructions.  
  
Please report any problems at http://bugs.mysql.com/  
  
The latest information about MySQL is available on the web at  
  
  http://www.mysql.com  
  
Support MySQL by buying support/licenses at http://shop.mysql.com  
  
New default config file was created as /usr/my.cnf and  
will be used by default by the server when you start it.  
You may edit this file to change server settings

这步要注意：数据库的版本与操作系统要一直，我的操作系统是64位所有数据库也必须是64位的。

使用rpm命令安装MySQL数据库客户端

安装完成之后，MySQL的默认数据文件存储在/var/lib/mysql下面，我们依据我们的要求将其存储在/usr/local/mysql_data下。

[root@localhost app]# rpm -ivh MySQL-client-5.6.21-1.linux_glibc2.5.x86_64.rpm   
Preparing...                ########################################### [100%]  
   1:MySQL-client           ########################################### [100%]

登陆MySQL

MySQL登录的命令是mysql， mysql 的使用语法如下：mysql [-u username] [-h host] [-p[password]] [dbname] username 与 password 分别是 MySQL用户名与密码，mysql的初始管理帐号是root，没有密码，注意：这个root用户不是Linux的系统用户。MySQL默认用户是 root，由于初始没有密码，第一次进时只需键入mysql即可。

[root@localhost ~]# mysql  
  
Welcome to the MySQL monitor. Commands end with ; or g.   
  
Your MySQL connection id is 1 to server version: 4.0.16-standard   
  
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.   
  
mysql>

出现了“mysql>”提示符，恭喜你，安装成功!

增加了密码后的登录格式如下：

mysql -u root -p

Enter password: (输入密码)

其中-u后跟的是用户名，-p要求输入密码，回车后在输入密码处输入密码。

可是我输入mysql却出错了：

初始密码是空的，我输入的也是空的还是错误!

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

MySQL安装解决方法：重改密码

[root@localhost ~]# /etc/init.d/mysql stop 这里也可以键入命令：service mysql stop  
  
[root@localhost ~]# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &   
  
[root@localhost ~]# mysql -u root mysql   
  
mysql> UPDATE user SET Password=PASSWORD('newpassword') where USER='root';   
  
mysql> FLUSH PRIVILEGES;   
  
mysql> quit   
  
[root@localhost ~]# /etc/init.d/mysqld restart 这里也可以键入命令：service mysql restart  
  
[root@localhost ~]# mysql -uroot -p   
  
Enter password:   
  
mysql>搞定!

开启mysql的远程访问权限

默认mysql的用户是没有远程访问的权限的，因此当程序跟数据库不在同一台服务器上时，我们需要开启mysql的远程访问权限。
主流的有两种方法，改表法和授权法。
相对而言，改表法比较容易一点，个人也是比较倾向于使用这种方法，因此，这里只贴出改表法

登陆mysql

mysql -u root -p

修改mysql库的user表，将host项，从localhost改为%。%这里表示的是允许任意host访问，如果只允许某一个ip访问，则可改为相应的ip，比如可以将localhost改为192.168.1.123，这表示只允许局域网的192.168.1.123这个ip远程访问mysql。

mysql> use mysql; 这个命令会报错，解决方法为：输入命令SET PASSWORD = PASSWORD('123456');重新设置一次MySQL登陆密码  
mysql> update user set host = '%' where user = 'root';   
mysql> select host, user from user;   
mysql> flush privileges;

防火墙开放3306端口

打开防火墙配置文件

vim  /etc/sysconfig/iptables

增加下面一行

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

重启防火墙

service  iptables restart

注意：增加的开放3306端口的语句一定要在icmp-host-prohibited之前

附：个人配置

# Firewall configuration written by system-config-firewall  
# Manual customization of this file is not recommended.  
*filter  
:INPUT ACCEPT [0:0]  
:FORWARD ACCEPT [0:0]  
:OUTPUT ACCEPT [0:0]  
-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT  
-A INPUT -p icmp -j ACCEPT  
-A INPUT -i lo -j ACCEPT  
-A INPUT -i eth0 -j ACCEPT  
-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT  
-A INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT  
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT  
-A FORWARD -m state --state ESTABLISHED,RELATED -j ACCEPT  
-A FORWARD -p icmp -j ACCEPT  
-A FORWARD -i lo -j ACCEPT  
-A FORWARD -i eth0 -j ACCEPT  
-A INPUT -j REJECT --reject-with icmp-host-prohibited  
-A FORWARD -j REJECT --reject-with icmp-host-prohibited  
COMMIT

用Navicat Premium工具链接Linux中的MySQL

找到并修改my.cnf文件。在不同的Linux系统下，my.cnf放在不同的位置。这里以Ubuntu Server做示例，其他系统请根据情况自行找到my.cnf的路径。一般只会存放在/etc/my.cnf或者/etc/mysql/my.cnf下。而在我这里的Linux系统下是在/usr/share/mysql这个目录中的my-default.cnf，你可以将它复制并且改名到其他目录中。

用vim打开my.cnf文件，然后找到[mysqld]部分的参数，在配置后面建立一个新行，添加下面这个参数：
skip-name-resolve
保存文件并重启MySQL：

service mysql restart

文章转载自WorkLifeRecords，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。