由于公司临时让将Oracle的数据移植到mysql上面,所以让我在公司服务器上面安装一下mysql。下面就是我的安装过程以及一些错误解决思路。其实对于不同版本安装大体都有差不多。
( Linux查看mysql 安装路径: whereis mysql 或运行文件: which mysql )
1. 从官网下载 mysql-5.7.13-linux-glibc2.5-x86_64.tar.gz
mysql-5.7.10-linux-glibc2.5-x86_64.tar.gz
mysql-5.7.11-linux-glibc2.5-x86_64.tar.gz
mysql-5.7.12-linux-glibc2.5-x86_64.tar.gz
mysql-5.7.13-linux-glibc2.5-x86_64.tar.gz
官网: MySQL :: Download MySQL Community Server
2.检查你所用的Linux下有没有安装过mysql,没有卸载干净
#rpm -qa|grep -i mysql mysql-5.7.13-linux-glibc2.5-x86_64 |
*可见已经安装了库文件,应该先卸载,不然会出现覆盖错误。注意卸:载时使用了--nodeps选项,忽略了依赖关系:
#rpm -e mysql-5.7.13-linux-glibc2.5-x86_64 --nodeps |
当然你的也可能不止这一个文件,也可能有多个,那么你就依次 rpm -e xx --nodeps 卸载,卸载完了再检查一下,若确定删除干净后,在经行下面步骤。
3. 创建mysql的用户组/用户, data目录及其用户目录
在这步之前一定要先确保你所建的用户以及用户组没有存在,要不然在后面的过程中会报错,删除时候要先删除用户在删除用户组名。
# userdel mysql # 删除用户 # groupdel mysql # 删除用户组名 # mkdir /home/mysql # 在home文件夹下创建文件夹mysql # mkdir /home/mysql/data # 在mysql文件夹下创建文件夹data # groupadd mysql # 创建一个名为mysql的用户组 # useradd -g mysql -d /home/mysql mysql # 在用户组下创建用户 |
4、解压安装包并将解压包里的内容拷贝到mysql的安装目录/home/mysql
# tar -xzvf mysql-5.7.13-linux-glibc2.5-x86_64.tar.gz # 解压文件 # cd mysql-5.7.13-linux-glibc2.5-x86_64 # 进入 # mv * /home/mysql # 移动到之前我创建的mysql文件夹下。 |
# cd /home/mysql # 进入安装目录 # ./bin/mysql_install_db --user=mysql --basedir=/home/mysql --datadir=/home/mysql/data [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize 2015-11-10 02:09:23 [WARNING] The bootstrap log isn't empty: 2015-11-10 02:09:23 [WARNING] 2015-11-10T10:09:18.114182Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead 2015-11-10T10:09:18.129343Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000) 2015-11-10T10:09:18.129408Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000) |
mysql5.7新特性:由上面可以看出, mysql_install_db 已经不再推荐使用了,建议改成 mysqld --initialize 完成实例初始化。
# ./bin/mysqld --user=mysql --basedir=/home/mysql --datadir=/home/mysql/data --initialize # ./mysqld --user=mysql --basedir=/home/mysql --datadir=/home/mysql/data --initialize [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). [ERROR] --initialize specified but the data directory has files in it. Aborting. 2016-04-08T01:46:53.155879Z 0 [ERROR] Aborting |
出现上面的错误是因为我们没有清空mysql的data目录, 执行清空命令如下:
# cd /home/mysql/data # 进入安装目录下的data目录 # rm -fr * # 清空数据 # cd /home/mysql # ./bin/mysqld --user=mysql --basedir=/home/mysql --datadir=/home/mysql/data --initialize [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2016-04-08T01:47:59.945537Z 0 [Warning] InnoDB: New log files created, LSN=45790 2016-04-08T01:48:00.333528Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2016-04-08T01:48:00.434908Z 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: ece26421-fd2b-11e5-a1e3-00163e001e5c. 2016-04-08T01:48:00.440125Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2016-04-08T01:48:00.440904Z 1 [Note] A temporary password is generated for root@localhost: **mjT,#x_5sW |
牢记上面的随机密码, 如上**mjT,#x_5sW, 下面我们修改密码时需要用到。
# cd /home/mysql # ./support-files/mysql.server start Starting MySQL.. OK! |
刚刚我用的是/home/mysql为mysql的安装目录basedir, 则在启动服务时会出现如下错误:
# ./support-files/mysql.server start ./support-files/mysql.server: line 276: cd: /usr/local/mysql: No such file or directory Starting MySQL ERROR! Couldn't find MySQL server (/usr/local/mysql/bin/mysqld_safe) |
由上面可知mysql的tar.gz安装包的默认安装目录为/usr/local/mysql, 这时候我们需要修改/support-files/mysql.server文件的basedir和datadir目录路径为我们环境所在的mysql的basedir和datadir路径, 如下:
# ./support-files/mysql.server start ./support-files/mysql.server: line 276: cd: /usr/local/mysql: No such file or directory Starting MySQL ERROR! Couldn't find MySQL server (/usr/local/mysql/bin/mysqld_safe) |
注意:上面 basedir 以及datadir部分是手动改的,那么在命令中如何手动改,步骤
1、 按 键盘 i 键(insert),然后就可以在你想要的位置编写了。
# ln -s /home/mysql/bin/mysql /usr/bin/mysql |
下面的前3步操作过程中,可能会出错,如果有错就跳过,因为不同电脑的环境不一样,
# mv /etc/my.cnf /etc/my.cnf.bak |
如果这一步报错,没有关系,正常情况下是由于/ect文件夹下没有my.cnf锁引起的,创建一下就可以了,或者你不会创建 , 直接使用 vim /etc/my.cnf 命令编辑 ,
然后直接退出编辑就可以,在执行这个命令就不会出错了,退出编辑命令 esc键 -> (shift键+ :(冒号键) -> wq! ),然后在执行上面的命令就不会报错了。
# cd /home/mysql/support-files |
# cp my-default.cnf /etc/my.cnf |
4、设置编码,可按需修改新的配置文件选项, 不修改配置选项, mysql则按默认配置参数运行.
如下是我修改配置文件/etc/my.cnf, 设置编码为utf8以防乱码
(在下文: 找不到my-default.cnf及my.cnf)
# vim /etc/my.cnf [mysqld] basedir = /home/mysql datadir = /home/mysql/data character_set_server=utf8 init_connect='SET NAMES utf8' [client] default-character-set=utf8 |
有的时候使用这个命令后,前3步有问题的这里可能my.cnf文件是空的,这个不要紧,既然没有生成,那么你就自己编辑喽!具体编辑怎么实现就参照步骤6.
# vim /etc/my.cnf [mysqld] basedir = /home/mysql datadir = /home/mysql/data character_set_server=utf8 init_connect='SET NAMES utf8' [client] default-character-set=utf8 |
如果在执行第一步就报错,则说明刚刚在第8步中配置出问题了,那你就重新设置一下
# service mysqld start # 启动服务 # service mysqld restart # 重启服务 # service mysqld stop # 停止服务 |
# cd /home/mysql # ./bin/mysqladmin -u root -p '**mjT,#x_5sW' password '123456' mysqladmin: # 刚刚之前的初始密码 [Warning] Using a password on the command line interface can be insecure. Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety. mysql> use mysql Database changed mysql> update user set authentication_string = PASSWORD('123456') where user = 'root'; Query OK, 0 rows affected, 1 warning (0.00 sec) Rows matched: 2 Changed: 0 Warnings: 1 mysql> \s -------------- mysql Ver 14.14 Distrib 5.7.13, for linux-glibc2.5 (x86_64) using EditLine wrapper Connection id: 3 Current database: mysql Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.13 MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 1 hour 29 min 17 sec Threads: 1 Questions: 50 Slow queries: 0 Opens: 136 Flush tables: 1 Open tables: 129 Queries per second avg: 0.009 -------------- |
如果这一步完成不了的话,也就是修改不了密码的话,你也可以直接下面的方式先登录
root@s0:/home/mysql# ./bin/mysqladmin -u root -p '946//EE=v!Ne' password '123456' mysqladmin: Enter password: mysqladmin: connect to server at 'localhost' failed --不让你修改 error: 'Access denied for user 'root'@'localhost' (using password: YES)' |
root@s0:/home/mysql# find / -name mysql.sock /tmp/mysql.sock root@s0:/home/mysql# /home/mysql/bin/mysql -u root -p -S /tmp/mysql.sock Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) --输入之前的密码 root@s0:/home/mysql# /home/mysql/bin/mysql -u root -p -S /tmp/mysql.sock Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.13 Copyright (c) 2000, 2016, 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> mysql> use mysql; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. --没有权限 mysql> alter user 'root'@'localhost' identified by 'root'; Query OK, 0 rows affected (0.04 sec) 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 authentication_string = PASSWORD('123456') where user = 'root'; Query OK, 1 row affected, 1 warning (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> \s |
但是如果不修改密码,不进每次登陆麻烦而且在执行一些操作会报一下错误
You must reset your password using ALTER USER statement before executing this statement # 意思就是你必须重置你的密码在执行这个execute操作前 |
这里教大家用另外一种方式修改密码,这个是在已经登陆进去的情况下依次执行。
mysql> SET PASSWORD = PASSWORD('123456'); # PASSWORD()里面的123456 是我设置的新密码,你也可以设置成你的密码 Query OK, 0 rows affected, 1 warning (0.00 sec) Rows matched: 2 Changed: 0 Warnings: 1 mysql> ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER; Query OK, 0 rows affected, 1 warning (0.00 sec) Rows matched: 2 Changed: 0 Warnings: 1 mysql> flush privileges; Query OK, 0 rows affected, 1 warning (0.00 sec) Rows matched: 2 Changed: 0 Warnings: 1 |
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> \s -------------- /usr/soft/mysql/bin/mysql Ver 14.14 Distrib 5.7.13, for linux-glibc2.5 (x86_64) using EditLine wrapper Connection id: 2 Current database: mysql Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.13 MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: latin1 ---需要修改成你想要的编码 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 27 sec Threads: 1 Questions: 43 Slow queries: 0 Opens: 136 Flush tables: 1 Open tables: 129 Queries per second avg: 1.592 -------------- mysql> show variables like 'character%'; +--------------------------+---------------------------------+ | Variable_name | Value | +--------------------------+---------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/soft/mysql/share/charsets/ | +--------------------------+---------------------------------+ 8 rows in set (0.00 sec) mysql> SET character_set_database = utf8; ----设置编码 Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show variables like 'character%'; +--------------------------+---------------------------------+ | Variable_name | Value | +--------------------------+---------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/soft/mysql/share/charsets/ | +--------------------------+---------------------------------+ 8 rows in set (0.00 sec) mysql> \s -------------- /usr/soft/mysql/bin/mysql Ver 14.14 Distrib 5.7.13, for linux-glibc2.5 (x86_64) using EditLine wrapper Connection id: 2 Current database: mysql Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.13 MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 3 min 28 sec Threads: 1 Questions: 52 Slow queries: 0 Opens: 137 Flush tables: 1 Open tables: 130 Queries per second avg: 0.250 -------------- 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> \s -------------- /usr/soft/mysql/bin/mysql Ver 14.14 Distrib 5.7.13, for linux-glibc2.5 (x86_64) using EditLine wrapper Connection id: 2 Current database: mysql Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.13 MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: latin1 ---需要修改成你想要的编码 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 27 sec Threads: 1 Questions: 43 Slow queries: 0 Opens: 136 Flush tables: 1 Open tables: 129 Queries per second avg: 1.592 -------------- mysql> show variables like 'character%'; +--------------------------+---------------------------------+ | Variable_name | Value | +--------------------------+---------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/soft/mysql/share/charsets/ | +--------------------------+---------------------------------+ 8 rows in set (0.00 sec) mysql> SET character_set_database = utf8; ----设置编码 Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show variables like 'character%'; +--------------------------+---------------------------------+ | Variable_name | Value | +--------------------------+---------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/soft/mysql/share/charsets/ | +--------------------------+---------------------------------+ 8 rows in set (0.00 sec) mysql> \s -------------- /usr/soft/mysql/bin/mysql Ver 14.14 Distrib 5.7.13, for linux-glibc2.5 (x86_64) using EditLine wrapper Connection id: 2 Current database: mysql Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.13 MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 3 min 28 sec Threads: 1 Questions: 52 Slow queries: 0 Opens: 137 Flush tables: 1 Open tables: 130 Queries per second avg: 0.250 -------------- mysql> |
grant all on *.* to 'root'@'%' identified by '123456'; |
如果不设置远程连接,用mysql数据库连接工具可是连接不上的哦!!!
首先要知道,远程连接的Linux系统的ip,确保本机已经能够ping通的情况下再连接。
到这里说明你的mysql安装成功了,是不是感觉特别简单啊!!!!
mac终端下修改MySQL的编码格式--找不到my-default.cnf及my.cnf
2.2 support-files文件夹里面没有my-default.cnf或my.cnf文件,那么就要在/etc下新建my.cnf
2.3 进行完上步操作后会进入vim模式,此时复制(***文本)的内容粘贴进去,,(不包含 ***文本 );
2.4 粘贴成功后注意看vim的第一行"#"有没有丢掉(本人就丢过~~哈哈~),都OK的话点esc退出编辑,
:wq! --保存后强制退出.[附几个编辑命令:dd 删除光标所在行; dw 删除一个字(word); x 删除当前字符].
# Example MySQL config file for medium systems.
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# The following options will be passed to all MySQL clients
# Here follows entries for some specific programs
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
# Replication Master Server (default)
# binary logging is required for replication
# binary logging format - mixed recommended
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
# Replication Slave (comment out master section to use this)
# To configure this host as a replication slave, you can choose between
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
# where you replace <host>, <user>, <password> by quoted strings and
# <port> by the master's port number (3306 by default).
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
# The replication master for this slave - required
# The username the slave will use for authentication when connecting
# The password the slave will authenticate with when connecting to
# The port the master is listening on.
# binary logging - not required for slaves, but recommended
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/mysql/data
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/data
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
# Remove the next comment character if you are not familiar with SQL
linux下mysql开启远程访问权限及防火墙开放3306端口
报错:2003-can't connect to mysql server on ' ' (10038)
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 25 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 11211 -j ACCEPT(新添加的)
-A RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited
// -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 11211 -j ACCEPT(新添加的)
报错: Access denied for user 'star'@'192.168.2.102' (using password: YES)
CREATE USER star IDENTIFIED BY '123456';
2. mysql给用户赋予所有权限:(在linux命令窗执行)
GRANT ALL PRIVILEGES ON *.* TO 'star'@"%" IDENTIFIED BY "123456";
格式:grant 权限 on 数据库名.表名 to 用户@登录主机 identified by “用户密码”;
报错:1130-host ... is not allowed to connect to this MySql server
可能是你的帐号不允许从远程登陆,只能在localhost。这个时候只要在localhost的那台电脑,登入mysql后,更改 "mysql" 数据库里的 "user" 表里的 "host" 项,从"localhost"改称"%"
mysql -u root -pvmwaremysql>use mysql;
mysql>update user set host = '%' where user = 'root';
mysql>select host, user from user;
例如,你想myuser使用mypassword从任何主机连接到mysql服务器的话。
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
如果你想允许用户myuser从ip为192.168.1.6的主机连接到mysql服务器,并使用mypassword作为密码
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
我用的第一个方法,刚开始发现不行,在网上查了一下,少执行一个语句 mysql>FLUSH RIVILEGES 使修改生效.就可以了
另外一种方法,不过我没有亲自试过的,在csdn.net上找的,可以看一下.
1、d:\mysql\bin\>mysql -h localhost -u root //这样应该可以进入MySQL服务器
2、mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root's password' WITH GRANT OPTION //赋予任何主机访问数据的权限