mysql5.6/7 二进制部署

环境：centos7

部署

1.安装依赖

yum -y install autoconf gcc gcc-c++ libaio

2.删除mariadb

rpm -qa | grep mariadb
yum remove 包的全称

3.创建用户组

useradd -s /sbin/nologin -g mysql -M mysql

4.解压到指定目录并赋权

mv mysql5.......  /usr/local/mysql

cd /usr/local

chown -R mysql:mysql mysql

5.初始化数据

1).5.6

cd /usr/local/mysql/scripts
./mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql
cd ..
cp -a ./support-files/my-default.cnf /etc/my.cnf
cp -a ./support-files/mysql.server /etc/init.d/mysqld

2).5.7

cd /usr/local/mysql
bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

会出来一个临时密码，记住。
cd ..
cp -a ./support-files/my-default.cnf /etc/my.cnf
cp -a ./support-files/mysql.server /etc/init.d/mysqld

3).编辑配置文件

vim /etc/my.cnf

log-bin=mysql-bin

max_binlog_size = 200M  #bin日志文件大小

expire_logs_days = 20  #日志保留天数（不能大于29天）

basedir = /usr/local/mysql

datadir = /usr/local/mysql/data

character-set-server = utf8

lower_case_table_names=1   #不区分大写和小写

innodb_buffer_pool_size = 4096M  #内存的1/4 最优为75%

innodb_log_file_size = 256M

max_connections = 512  #最大连接数

thread_concurrency = 16  #设置并发数，默认设置为当前CPU核数\*2

启动并配置用户

1.配置环境变量

vim /etc/profile

export MYSQL_HOME=/usr/local/mysql

export PATH=$PATH:$MYSQL_HOME/bin

source /etc/profile

cd /etc/init.d && ./mysqld start

2.初始化密码

1).5.6

#mysqladmin -uroot -password localhost.localdomain 'vany_mysql@123'

msyql > SET PASSWORD FOR 'root'@'localhost' = PASSWORD('vany_mysql@123');

msyql > GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'vany_mysql@123'   #允许root用户远程访问

mysql>FLUSH PRIVILEGES;   **#登出前一定要刷新权限！！！**！

2).5.7

mysql -uroot -p #临时密码登录

必须修改掉临时的密码，不然登陆以后，不管运行任何命令，总是提示这个
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> use mysql;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

解决方式如下：

MySQL版本5.7.6版本以前用户可以使用如下命令：

mysql> SET PASSWORD = PASSWORD('vany_mysql@123');

MySQL版本5.7.6版本开始的用户可以使用如下命令：

mysql> ALTER USER USER() IDENTIFIED BY 'vany_mysql@123';

以上输入重置临时密码后便可正常操作mysql


mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'vany_mysql@123' WITH GRANT OPTION;     #允许root用户远程访问

mysql>FLUSH PRIVILEGES;     #登出前一定要刷新权限！！！！

3).配置连接用户跟数据库

创建用户
mysql> create user 'dep'@'%' identified by 'vany_mysql@123';
创建utf8格式的生产数据库
msyql> CREATE DATABASE `message...` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
赋予dep用户操作该数据的所有权限
mysql> grant all on message....* to 'dep'@'%'WITH GRANT OPTION;

mysql> FLUSH PRIVILEGES;     #登出前一定要刷新权限！！！！