CentOS 下安装Mysql5.7
一、建立mysql安装目录及数据存放目录
# mkdir -p /usr/local/mysql/data
# mkdir -p /usr/local/mysql/install
# mkdir -p /usr/local/mysql/sock
二、创建用户和用户 组
#groupadd mysql
#useradd -g mysql mysql
三、赋予数据存放目录权限
#chown –R mysql.mysql /usr/local/mysql/install
#chown –R mysql.mysql /usr/local/mysql/sock
四、解压MYSQL
#tar zxvf mysql-5.7.14.tar.gz
五、编译MYSQL
#cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql/install \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/sock/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS:STRING=utf8,gbk \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=/usr/local/boost/ \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DMYSQL_USER=mysql \
-DMYSQL_TCP_PORT=3306
或cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql/install \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/sock/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS:STRING=utf8,gbk \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_BOOST=/usr/local/boost/ \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_DATADIR=/home/mysql/data/ \
-DMYSQL_USER=mysql \
-DMYSQL_TCP_PORT=3306
#make
#make install
六、复制配置文件
5.7版本不需要此操作
# cp support-files/my-medium.cnf /etc/my.cnf
七、初始化数据库
5.7版本不需要此操作
# chmod 755 scripts/mysql_install_db //执行前需赋给scripts/mysql_install_db文件执行权限
# scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/install/ --datadir=/home/mysql/data
注:basedir:mysql安装路径 datadir:数据库文件储存路径
5.6版本需要此操作(在安装目录)
./mysql_install_db --user=mysql --basedir=/usr/local/mysql/install --datadir=/home/mysql/data --pid-file=/home/mysql/data/mysql.pid
先去安装目录 cd /usr/local/mysql/install/bin
再执行数据库初始化
./mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql/install --socket=/usr/local/mysql/sock/mysql.sock
//在MySQL 5.7.6版本以前是 bin/mysql_install_db --user
八、设置mysqld的开机启动
# cp support-files/mysql.server /etc/init.d/mysql
# chmod 755 /etc/init.d/mysql
# chkconfig --add mysql
# chkconfig mysql on
增加数据库软连接
ln -s /usr/local/mysql/install/bin/mysql /usr/bin
ln -s /usr/local/mysql/install/bin/mysqladmin /usr/bin
可选配置:为MySQL配置环境变量
将mysql的bin目录加到PATH中,有利于以后管理和维护,在/etc/profile中加入myslq/bin,同时增加两个别名方便操作:
# export PATH=/usr/local/mysql/bin:$PATH
# alias mysql_start="mysqld_safe &"
# alias mysql_stop="mysqladmin –u root -p shutdown"
九、启动mysql服务
# /etc/init.d/mysql start
十、为root帐户设置初始密码
//第一次登录MySQL,密码文件在,也可以从make install 最后的一行看到密码
如不知密码,可能设置无密码登陆,/etc/my.cnf
[mysqld]
增加
skip-grant-tables
保存,重新重启mysql
进去重置密码
update user set authentication_string=PASSWORD('newpass') where User='root';
flush privileges;
初次登陆提示修改密码
set password=password(‘密码’);
或SET PASSWORD FOR 'root'@localhost = PASSWORD('123456');
退出后,修改/etc/my.cnf把skip-grant-tables注销 # skip-grant-tables
重启数据库 service mysql restart
十一、删除本机匿名连接的空密码帐号
注:先本机登录mysql
mysql>use mysql; //选择默认数据库mysql
mysql> SET PASSWORD = PASSWORD('brlf!@#123');
mysql>flush privileges;
mysql>exit
参考:设置MySQL远程连接
mysql>use mysql
mysql>select user,password,host from user;
mysql>update user set host = '192.168.%' where user = '127.0.0.1';
mysql>grant all privileges on *.* to root@'%' identified by "root123";//给以root@ip登录的远程连接赋予权限,能够连接数据库。远程无法连接的常见问题原因。并把远程登录用户的密码设置为root
mysql> flush privileges;
mysql>quit
MySQL 5.7版本sql_mode=only_full_group_by问题
set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
修改my.cnf
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';