安装Mysql
1.下载
[root@localhost soft]# wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz
2.：解压文件
[root@dbserver /]# tar -xzvf mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
3.修改文件
[root@dbserver local]# mv mysql-5.7.19-linux-glibc2.12-x86_64 mysql
4：检查库文件是否有删除，若有便删除（linux系统自带的）
[root@dbserver mysql]# rpm -qa | grep mysql
删除
[root@dbserver mysql]# rm -e –-nodeps mysql-libs-5.1.52.x86_64
5：检查mysql组和用户是否存在，如无创建
[root@dbserver ~]# cat /etc/group | grep mysql
[root@dbserver ~]# cat /etc/passwd |grep mysql
创建
[root@dbserver ~]#groupadd mysql [root@dbserver ~]#useradd -r -g mysql mysql //useradd -r参数表示mysql用户是系统用户，不可用于登录系统
6：在mysql下添加data目录
[root@dbserver mysql]# mkdir data
7：更改mysql目录下所有的目录及文件夹所属组合用户
[root@dbserver mysql]# cd /usr/local/ [root@dbserver local]# chown -R mysql mysql/ [root@dbserver local]# chgrp -R mysql mysql/ [root@dbserver local]# cd mysql/ [root@dbserver mysql]# ls -l total 40 drwxr-xr-x. 2 mysql mysql 4096 Aug 31 16:45 bin -rw-r--r--. 1 mysql mysql 17987 Jun 22 22:13 COPYING drwxr-xr-x. 2 mysql mysql 6 Aug 31 16:48 data drwxr-xr-x. 2 mysql mysql 52 Aug 31 16:45 docs drwxr-xr-x. 3 mysql mysql 4096 Aug 31 16:44 include drwxr-xr-x. 5 mysql mysql 4096 Aug 31 16:45 lib drwxr-xr-x. 4 mysql mysql 28 Aug 31 16:45 man -rw-r--r--. 1 mysql mysql 2478 Jun 22 22:13 README drwxr-xr-x. 28 mysql mysql 4096 Aug 31 16:45 share drwxr-xr-x. 2 mysql mysql 86 Aug 31 16:45 support-files
8：安装和初始化数据库
安装
./mysqld --initialize --user=mysql --basedir=/usr/local/mysql/--datadir=/usr/local/mysql/data/ 2017-08-31T08:50:23.910440Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2017-08-31T08:50:23.910635Z 0 [ERROR] Can't find error-message file '/usr/local/mysql/--datadir=/usr/local/mysql/data/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' con figuration directive.2017-08-31T08:50:24.709286Z 0 [Warning] InnoDB: New log files created, LSN=45790 2017-08-31T08:50:24.767540Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2017-08-31T08:50:24.892629Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 6e083b8f-8e29-11e7-88b1- 005056b427be.2017-08-31T08:50:24.895674Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2017-08-31T08:50:24.896645Z 1 [Note] A temporary password is generated for root@localhost: gFamcspKm2+u
报错[ERROR] Can’t find error-message file ‘/usr/local/mysql/–datadir=/usr/local/mysql/data/share/errmsg.sys’. Check error-message file location and ‘lc-messages-dir’ con
解决
[root@dbserver bin]# ./mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/ --lc_messages_dir=/usr/local/mysql/share --lc_messages=en_US 2017-08-31T09:00:54.941514Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2017-08-31T09:00:56.364312Z 0 [Warning] InnoDB: New log files created, LSN=45790 2017-08-31T09:00:56.602211Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2017-08-31T09:00:56.668145Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: e69986d2-8e2a-11e7-a335- 005056b427be.2017-08-31T09:00:56.671464Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2017-08-31T09:00:56.672453Z 1 [Note] A temporary password is generated for root@localhost: qfuqvCsHb2!.
9配置my.cnf
接下来进入/usr/local/mysql/support-files/目录下
查看是否存在my-default.cnf文件，如果存在直接copy到/etc/my.cnf文件中
[root@dbserver mysql]# cp -a ./support-files/my-default.cnf /etc/my.cnf
如果不存在my-default.cnf文件,则在/etc/目录下创建my.cnf,并写入以下内容
#[mysql] #basedir=/usr/local/mysql/ #datadir=/usr/local/mysql/data/
也可以用这种的
这种的pid 有个问题 请慎重
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M #skip-grant-tables=1 #datadir=/var/lib/mysql datadir=/data00/mysql_data/mysql #socket=/var/lib/mysql/mysql.sock socket=/data00/mysql_data/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid skip-name-resolve query_cache_type=1 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
10启动服务
[root@dbserver mysql]# cd bin/ [root@dbserver bin]# ./mysqld_safe --user=mysql & [2] 10436 [root@dbserver bin]# Logging to '/var/log/mysql/mysql.log'. 2017-08-31T09:52:15.806633Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 2017-08-31T09:52:16.292949Z mysqld_safe mysqld from pid file /var/run/mysql/mysql.pid ended
11将mysqld服务加入开机自启动项。
将{mysql}/ support-files/mysql.server 拷贝为/etc/init.d/mysql并设置运行权限，这样就可以使用service mysql命令启动/停止服务，
否则就只能使用{mysql}/bin/mysqld_safe &命令来启动服务
还需要把mysql.server中basedir的相关路径，改为自定义的路径，默认路径是/usr/local/mysql
[root@dbserver support-files]# cp mysql.server /etc/init.d/mysql [root@dbserver support-files]# chmod +x /etc/init.d/mysql -- 把mysql注册为开机启动的服务 [root@dbserver support-files]# chkconfig --add mysql -- 查看是否添加成功 [root@dbserver support-files]# chkconfig --list mysql Note: This output shows SysV services only and does not include native systemd services. SysV configuration data might be overridden by native systemd configuration. If you want to list systemd services use 'systemctl list-unit-files'. To see services enabled on particular target use 'systemctl list-dependencies [target]'. mysql 0:off 1:off 2:on 3:on 4:on 5:on 6:off
12启动服务
[root@dbserver bin]# service mysql start Starting MySQL.Logging to '/var/log/mysql/mysql.log'. ERROR! The server quit without updating PID file (/var/lib/mysql/dbserver.pid).
解决
[root@dbserver mysql]# rm /etc/my.cnf rm: remove regular file '/etc/my.cnf'? y [root@dbserver mysql]# /etc/init.d/mysql start Starting MySQL.Logging to '/usr/local/mysql/data/dbserver.err'. SUCCESS! [root@dbserver mysql]# service mysql start Starting MySQL SUCCESS!
13登录mysql
[root@dbserver bin]# ./mysql -u root -p
密码是第八步产生的密码
14设置密码
mysql> set password=password("root");
Query OK, 0 rows affected, 1 warning (0.00 sec)
注意不要使用单引号，为什么？你自己试试就知道了
15设置远程登录权限
mysql> grant all privileges on *.* to'root' @'%' identified by 'root'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.06 sec) mysql> quit Bye
请注意防火墙问题
开启远程连接
查看端口
netstat -an | grep 3306
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN
开启 MySQL 远程访问权限： 在linux系统上登陆mysql服务。
-- root 是用户名 [root@localhost ~]# mysql -u root -p Enter password: -- 输入密码
创建远程连接 MySQL 的用户：
-- 创建用户、密码及权限范围 第一个 roo t为用户名 @后为适用的主机，‘%’表示所有电脑都可以访问连接，第二个 root 为密码 mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.0.2' IDENTIFIED BY 'root' WITH GRANT OPTION; Query OK, 0 rows affected (1.57 sec) -- 立即生效 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
查看数据库用户：
复制代码 -- 使用 mysql 库 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 DISTINCT CONCAT('User: [', user, '''@''', host, '];') AS USER_HOST FROM user; +---------------------------------------+ | USER_HOST | +---------------------------------------+ | User: [root'@'127.0.0.1]; | | User: [root'@'192.168.0.2]; | | User: [root'@'::1]; | | User: [root'@'localhost]; | | User: [root'@'localhost.localdomain]; | +---------------------------------------+ 5 rows in set (0.00 sec)
创建成功。
查看端口：
mysql> show global variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3306 |
+---------------+-------+
1 row in set (0.01 sec)
开启3306端口：
[root@localhost ~]# vim /etc/sysconfig/iptables # 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 -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT -A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT -A INPUT -j REJECT --reject-with icmp-host-prohibited -A FORWARD -j REJECT --reject-with icmp-host-prohibited COMMIT "/etc/sysconfig/iptables" 14L, 543C
注意：一定要加在后两行的前面。
重启防火墙
[root@localhost ~]# service iptables restart
iptables：将链设置为政策 ACCEPT：filter [确定]
iptables：清除防火墙规则： [确定]
iptables：正在卸载模块： [确定]
iptables：应用防火墙规则： [确定]
查看服务器ip地址：
[root@localhost ~]# ifconfig eth0 Link encap:Ethernet HWaddr 00:0C:29:16:F4:E6 inet addr:192.168.0.123 Bcast:192.168.0.255 Mask:255.255.255.0 inet6 addr: hjjj:jjji::iii:oooo:oooo:ioio/64 Scope:Link UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:2931 errors:0 dropped:0 overruns:0 frame:0 TX packets:1631 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:322681 (315.1 KiB) TX bytes:266043 (259.8 KiB) lo Link encap:Local Loopback inet addr:127.0.0.1 Mask:255.0.0.0 inet6 addr: ::1/128 Scope:Host UP LOOPBACK RUNNING MTU:16436 Metric:1 RX packets:0 errors:0 dropped:0 overruns:0 frame:0 TX packets:0 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:0 RX bytes:0 (0.0 b) TX bytes:0 (0.0 b)
查看端口
root@iZ941qpiyjvZ:/etc/init.d# netstat -nap | grep 3306
tcp6 0 0 :::3306 :::* LISTEN
下面你还要确认的一件事就是确认客户端用户是否具有权限
mysql>grant all privileges on . to username@”%” identified by “password”;
注意%后面要空一隔