mysql rpm 安装6_RHEL 6.7利用rpm完整包安装MySQL-5.6.30

利用rpm完整包安装MySQL(MySQL-server-5.6.30 for rhel 6.7_64bit)

一、检查操作系统上是否安装了MySQL

[root@mydb1 backup]# rpm -qa |grep mysql

qt-mysql-4.6.2-26.el6_4.x86_64

mysql-5.1.71-1.el6.x86_64

mysql-server-5.1.71-1.el6.x86_64

mysql-devel-5.1.71-1.el6.x86_64

mysql-libs-5.1.71-1.el6.x86_64

二、删除MyQL(RedHat6.7服务器自带的mysql-libs是mysql-libs-5.1.61-4.el6.x86_64，可能会和高版本的mysql-libs有冲突)

[root@mydb1 backup]# rpm -qa |grep mysql*

[root@mydb1 backup]# yum -y remove mysql-libs-5.1.73-5.el6_6.x86_64

三、RPM安装MySQL(用root用户安装,不需要提前建mysql组和mysql用户，安装mysql-server过程中会自动创建)

MySQL-6.30 Yum Repository

rhel6

http://repo.mysql.com//mysql57-community-release-el6-8.noarch.rpm

rhel7

http://repo.mysql.com//mysql57-community-release-el7-8.noarch.rpm

MySQL Yum源官方说明：

http://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en

# Enable to use MySQL

5.7

[mysql57-community]

name=MySQL 5.7

Community Server

baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/

enabled=1

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

# Enable to use MySQL

5.6

[mysql56-community]

name=MySQL 5.6

Community Server

baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/6/$basearch/

enabled=1

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

下载rpm完整包安装MySQL

[root@mydb1 backup]# wget http://cdn.mysql.com//Downloads/MySQL-5.6/MySQL-5.6.30-1.el6.x86_64.rpm-bundle.tar

[root@mydb1 backup]# tar -xvf MySQL-5.6.30-1.el6.x86_64.rpm-bundle.tar[root@mydb1 backup]# rpm -pql MySQL-server-5.6.23-1.el6.x86_64.rpm | more

[root@mydb1 backup]# rpm -ivh MySQL-server-5.6.30-1.el6.i686.rpm

[root@mydb1 backup]# rpm -ivh MySQL-client-5.6.30-1.el6.x86_64.rpm

or

[root@mydb1 backup]#yum -y install MySQL-server* MySQL-client*

四、创建安装目录

[root@mydb1 backup]#mkdir -p /u01/app/mysql/data/mysql3306/{data,log,iblog,binlog,tmp}

五、配置my.cnf

[root@wmserpmysql1 ~]# cp -v /usr/my.cnf /u01/app/mysql/data/mysql3306/my.cnf

[root@wmserpmysql1 ~]# vim /u01/app/mysql/data/mysql3306/my.cnf

[client]

port=3306

socket=/u01/app/mysql/data/mysql3306/run/mysql.sock

[mysql]

port=3306

promprt=\\u@\\d \\r:\\m:\\s>

[mysqld]

default-storage-engine=INNODB

character-set-server=utf8

explicit_defaults_for_timestamp=true

#dir

basedir=/usr

datadir=/u01/app/mysql/data/mysql3306/data

tmpdir=/u01/app/mysql/data/mysql3306/tmp

innodb_log_group_home_dir=/u01/app/mysql/log/mysql3306/iblog

innodb_data_home_dir=/u01/app/mysql/log/mysql3306/iblog

slave_load_tmpdir=/u01/app/mysql/data/mysql3306/tmp

log-error=/u01/app/mysql/data/mysql3306/log/alert.log

slow_query_log_file=/u01/app/mysql/data/mysql3306/log/slow.log

relay_log_info_file=/u01/app/mysql/log/mysql3306/binlog/relay-log.info

master-info-file=/u01/app/mysql/log/mysql3306/binlog/master.info

socket=/u01/app/mysql/data/mysql3306/run/mysql.sock

log-bin=/u01/app/mysql/log/mysql3306/binlog/binlog

relay-log=/u01/app/mysql/log/mysql3306/binlog/relaylog

innodb_force_recovery=0

六、执行MySQL安装脚本

[root@wmserpmysql1 ~]# sh init3306.sh

rm -rf /u01/app/mysql/mysql3306/data/*

rm -rf /u01/app/mysql/log/mysql3306/iblog/*

rm -rf /u01/app/mysql/log/mysql3306/binlog/*

chmod -R 777 /u01/app/mysql/data/mysql/3306/data/*

chmod -R 777 /u01/app/mysql/log/mysql3306/iblog/*

chmod -R 777 /u01/app/mysql/log/mysql3306/binlog/*

chmod 755 /u01/app/mysql/data/mysql3306/my.cnf

mysql_install_db --defaults-file=/u01/app/mysql/data/mysql3306/my.cnf --basedir=/usr/ --datadir=/u01/app/mysql/data/mysql3306/data

七、查是否有MySQL服务，如没有添加mysql到服务开机启动

chkconfig --add mysqld

chkconfig --level 2345 mysqld on

查看开机启动设置是否成功

[root@mydb1 ~]# chkconfig --list | grep mysql

mysql          0:off  1:off  2:on    3:on    4:on    5:on    6:off

八、启动MySQL

[root@mydb1 ~]# service mysql start

Starting MySQL SUCCESS!

九、查看MySQL运行状态的方法

[root@mydb1 ~]# service mysql status

SUCCESS! MySQL running (4104)

十、查MySQL初始密码

[root@mydb1 ~]# cat /root/.mysql_secret

# The random password set for the root user at Mon Mar 30 15:04:58 2015 (local time): B15xIBTq3xAoGRVx

十一、登录MySQL

[root@mydb1 ~]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.6.23

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>

十二、修改密码

mysql> show databases;

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

mysql> SET PASSWORD = PASSWORD('guoyJoe123');

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

+--------------------+

4 rows in set (0.00 sec)

使用root用户进行远程访问：

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'mysql' WITH GRANT OPTION;

十三、my.cnf参考：

MySQL系统参数配置

先不启动MySQL，拷贝数据目录到新的位置/mysqldata，并修改权限

[root@wmserpmysql1 /]#cd /var/lib/mysql/

[root@wmserpmysql1 /]#cp -r * /mysqldata/

[root@wmserpmysql1 /]# chown mysql:mysql -R /mysqldata

删除原来默认的参数文件

[root@wmserpmysql1 mysql]# rm /usr/my.cnf

rm: remove regular file `/usr/my.cnf'? y

重新拷贝新的参数文件，然后修改

[root@wmserpmysql1 mysql]# pwd

/usr/share/mysql

[root@wmserpmysql1 mysql]# cp my-default.cnf /etc/my.cnf

[root@wmserpmysql1 mysql]# vim /etc/my.cnf

修改参数文件/etc/my.cnf

[client]

port = 3306

socket = /tmp/mysql.sock

[mysql]

no-auto-rehash

[mysqld]

port = 3306

socket = /tmp/mysql.sock

datadir=/mysqldata

skip-name-resolve

character-set-server=utf8

lower_case_table_names=1

expire-logs-days=60

back_log = 500

max_connections = 5000

max_connect_errors =100000

table_open_cache = 2048

max_allowed_packet = 16M

binlog_cache_size = 1M

max_heap_table_size = 64M

sort_buffer_size = 8M

join_buffer_size = 8M

thread_cache_size = 8

thread_concurrency =16

query_cache_size = 64M

query_cache_limit = 2M

ft_min_word_len = 4

thread_stack = 192K

tmp_table_size = 64M

log-bin=mysql-bin

binlog_format=mixed

slow_query_log_file=/mysqldata/localhost-slow.log

slow_query_log

long_query_time=1

relay-log=/mysqldata/relay-bin

server-id = 1

key_buffer_size = 32M

myisam_repair_threads = 1

myisam_recover

transaction_isolation = REPEATABLE-READ

innodb_additional_mem_pool_size = 16M

innodb_buffer_pool_size = 10G

innodb_data_file_path = ibdata1:10M:autoextend

innodb_file_per_table=1

innodb_file_io_threads = 4

innodb_thread_concurrency = 16

innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size = 8M

innodb_log_file_size = 256M

innodb_log_files_in_group = 6

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

innodb_flush_method=O_DIRECT

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqldump]

quick

max_allowed_packet = 16M

[myisamchk]

key_buffer_size = 512M

sort_buffer_size = 512M

read_buffer = 8M

write_buffer = 8M

[mysqlhotcopy]

interactive-timeout

[mysqld_safe]

open-files-limit = 65535

log-error=/mysqldata/wmserpmysql1.800best.com.err

pid-file=/mysqldata/wmserpmysql1.800best.com.pid

十四、查看及设置连接状态

MySQL的最大连接数默认是100

客户端登录：

mysql -uusername -ppassword

设置新的最大连接数为200：

mysql> set GLOBAL max_connections=200

显示当前运行的Query：

mysql> show processlist

显示当前状态：

mysql> show status;

退出客户端：

mysql> exit

查看当前最大连接数：

mysqladmin -uusername -ppassword variables |find "max_con"

如：

C:\mysql\bin>mysqladmin -uroot -p variables | find "max_con"

Enter password:

| max_connections | 100

| max_connect_errors | 100

十五、查询MySQL数据表的存储引擎类型

提问: 想要知道MySQL数据库是MyISAM还是Innodb类型。该如何检查MySQL数据库表的类型？

MySQl使用的存储引擎主要有两种：MyISAM和Innodb。MyISAM是非事务的，因此拥有读取更快，然而InnoDB完全支持细颗粒度的事务锁定(比如：commit/rollback)。当创建一张新的MySQL表时，选择它的类型(也就是存储引擎)。如果没有选择，就会使用与预设置的默认引擎。如果想要知道已经存在的MySQL数据表的类型，以下几种方法可以达到。

方法一

访问phpMyAdmin，从phpMyAdmin找出默认的数据库类型。从phpMyAdmin中选中数据库来查看它的表列表。在“Type”一列的下面，将会看到每个表的数据表类型。

方法二

可以直接登录MySQL服务器，另外一种鉴别存储引擎的方法是登录MySQL服务器后运行下面的MySQL命令：

mysql> SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA ='my_database' AND TABLE_NAME ='my_table';

上面的命令会显示在'mydatabase'数据库中'mytable'表的引擎类型。

方法三

还有一种检查引擎的方法是使用mysqlshow,是一种命令行下的显示数据库信息的工具。mysqlshow在MySQL 客户端安装包中有。要使用mysqlshow，需要提供MySQL服务器登录凭据。

下面的命令会显示特定的数据库信息。在“Engine”一列下面，可以看到每个表使用的引擎。

$ mysqlshow -u -p -i