mysql5.6 linux下安装笔记
由于现在要用mysql,以前一直用oracle ,现在下个mysql都要FQ下载,官方网站都下不了,选择之后都是windows版本。
最后找了个外国网站:http://fossies.org/linux/misc/mysql-5.6.25-linux-glibc2.5-x86_64.tar.gz/ 下载了最新版本的:mysql-5.6.25-linux-glibc2.5-x86_64.tar.gz
查看当前centos版本和内核,位数。
[root@localhost ~]# cat /etc/redhat-release --/etc/redhat-release配置文件用一行内容来声明 Red Hat 的名称和版本号。 由 rc.local 使用.
CentOS release 6.5 (Final) -- 发现我的是6.5版本。
[root@localhost ~]# cat /proc/version --64位系统
Linux version 2.6.32-431.el6.x86_64 (mockbuild@c6b8.bsys.dev.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-4) (GCC) ) #1 SMP Fri Nov 22 03:15:09 UTC 2013
[root@localhost ~]# uname -a
Linux localhost.localdomain 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22 03:15:09 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
一、把mysql压缩包上传到/usr/mysql 目录下面。安装目录选择/usr/local/mysql。
然后在网上找一篇安装mysql的文章,最后参考了http://blog.csdn.net/zhanngle/article/details/41042631
-------------------------------------------
datadir=/software/mysql-5.6.21/data
vim /etc/profile
mysql -u root mysql
UPDATE user SET password=PASSWORD("new_password") WHERE user='root';
FLUSH PRIVILEGES;
二、按上面的文章安装完之后
1、查询当前安装的mysql版本
[root@localhost ~]# mysql -V
mysql Ver 14.14 Distrib 5.6.25, for linux-glibc2.5 (x86_64) using EditLine wrapper
进入mysql、查看mysql版本
方法一:status;
方法二:select version();
2、Mysql启动、停止、重启常用命令
a、启动方式
1、使用 service 启动:
[root@localhost /]# service mysqld start (5.0版本是mysqld)
[root@szxdb etc]# service mysql start (5.5.7版本是mysql)
2、使用 mysqld 脚本启动:
/etc/inint.d/mysqld start
3、使用 safe_mysqld 启动:
safe_mysqld&
b、停止
1、使用 service 启动:
service mysqld stop
2、使用 mysqld 脚本启动:
/etc/inint.d/mysqld stop
3、mysqladmin shutdown
c、重启
1、使用 service 启动:
service mysqld restart
service mysql restart (5.5.7版本命令)
2、使用 mysqld 脚本启动:
/etc/init.d/mysqld restart
三、赋予权限
(1)、创建用户:
create user 'myuser'@'localhost' IDENTIFIED by '123456';
flush privileges;
(2)、创建数据库:
create database mydb;
flush privileges;
(3)、给用户授权数据库相关权限
grant all privileges on mydb.* to myuser@localhost identified by '123456';
flush privileges;
我用到的权限命令,简单说明,mydb为数据库实例名,myuser为用户名,后面对某个ip作对应的权限控制。
grant 为赋予权限,revoke为撤销权限,赋予权限完之后要用flush privileges; 来刷新生效。
1、grant all on mydb.*
to 'myuser'@'192.168.1.6';
2、grant select on mydb.* to 'myuser'@'192.168.1.%';
3、revoke all on mydb.* from 'myuser'@'192.168.1.6';
查看用户权限
show grants for 你的用户
比如:
show grants for root@’localhost’;
mysql> use mysql;
Database changed
mysql> select user,host from user;
具体参考:http://www.cnblogs.com/llsun/archive/2013/08/06/3240963.html
四、导入数据问题解决
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
1.执行批量的MYSQL语句,例如备份恢复的时候,时间过长; 或者SQL语句过大或者语句中含有BLOB类似字段--我的是BLOB字段存放文件导致过大引起
对应:修改my.cnf中的wait_timeout和interactive_timeout变量
在一些不便修改的情形下,如租用的空间,可以采取sql语句修改,如何做呢?
(1)、如果是租用空间,无法修改数据库参数,那么临时mysql语句设置,不过下次重新导入必须再设置:
#对当前交互链接有效;
mysql>set wait_timeout = someIntValue;
#对后续起的交互链接有效;
mysql>set interactive_timeout = someIntValue;
mysql>set global max_allowed_packet=1000000000; -- 设置针对BLOB字段过大导入引起的问题
(2)、如果是自己的服务器,那么找到my.ini(windows下my.ini)文件,linux发现是/usr/local/mysql下的my.cnf文件。
添加一句max_allowed_packet=16M,如果不行将16M再加大
加大wait_timeout也可起一定作用
eg:
wait_timeout=2880000
interactive_timeout = 2880000
max_allowed_packet = 100M
应用时记住重启数据库哦~~
除此之外,可能还有一些别的buffer_size的变量会影响到,也值得注意
例如
read_buffer_size
read_rnd_buffer_size
重启后查询
mysql> show variables like '%timeout';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 2880000 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| wait_timeout | 2880000 |
+-----------------------------+----------+
12 rows in set (0.00 sec)
mysql> show VARIABLES like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| max_allowed_packet | 104857600 |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)
五、安装完之后访问参考
http://blog.csdn.net/cuker919/article/details/44997923
六、启动应用
启动应用访问发现应用日志连接池报:java.sql.SQLException: Access denied for user 'myuser'@'localhost' (using password: YES)
同时切换到linux,用该用户试试:
[root@localhost ]# mysql -u myuser-p
Enter password:
ERROR 1045 (28000): Access denied for user 'myuser'@'localhost' (using password:
但发现mysql workbench用该用户访问一切正常,因为我是用mysql workbench创建的用户,但到linux又登录不了。
下面是试错的过程,但既然后面可以登录了
(1)、修改密码
[root@localhost ]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 614
Server version: 5.6.25 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, 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> 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"; --更改root用户密码
Query OK, 3 rows affected (0.00 sec)
Rows matched: 5 Changed: 3 Warnings: 0
mysql> update user set password=password("123456") where user="myuser"; --更改myuser用户密码
Query OK, 4 rows affected (0.00 sec)
Rows matched: 5 Changed: 4 Warnings: 0
mysql> exit --退出
(2)、再登录,还是报错
[root@localhost logs]# mysql -u myuser-p
Enter password:
ERROR 1045 (28000): Access denied for user 'myuser'@'localhost' (using password:
(3)、再试错。
[root@localhost ]# /etc/init.d/mysql stop --停止服务
Shutting down MySQL..... SUCCESS!
[root@localhost ]# /usr/bin/mysqld_safe --skip-grant-tables
-bash: /usr/bin/mysqld_safe: No such file or directory
[root@localhost ]# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
[1] 1791
[root@localhost ]# 150613 11:20:01 mysqld_safe Logging to '/usr/local/mysql/ data/localhost.localdomain.err'.
150613 11:20:02 mysqld_safe Starting mysqld daemon with databases from /usr/loca l/mysql/data
^C --按ctrl + c 退出
[root@localhost ]# mysqld_safe --user=mysql --skip-grant-tables --skip-networking
150613 11:21:27 mysqld_safe Logging to '/usr/local/mysql/data/localhost.localdom ain.err'.
150613 11:21:27 mysqld_safe A mysqld process already exists
[root@localhost ]# /etc/rc.d/init.d/mysql restart --启动服务
[root@localhost ]# mysql -u myuser-p -- 再登录,既然可以了,然后应用也可以登录
(4)、总结
先登录 mysql
# mysql -u root -p
mysql> use mysql
--切换
mysql> select
host,user,Password from user; --查看用户和密码情况,其实从这里就可以发现问题,不行就往下
# /etc/init.d/mysql stop
# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
# mysql -u root -p
mysql> UPDATE user SET Password=PASSWORD('newpassword') where USER='root';
mysql> FLUSH PRIVILEGES;
mysql> quit
# /etc/init.d/mysqld restart
# mysql -u myuser -p
参考:http://www.bxl.me/zh-cn/2567.html
http://blog.csdn.net/lyflower/article/details/6137021
http://blog.csdn.net/vurtne_ye/article/details/26514499
版权声明:本文为博主原创文章,未经博主允许不得转载。