mysql5.7.20搭建

2018/03/29 13:28
阅读数 8

官网mysql下载源码安装


下载MySQL5.7.20源码包,
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.20.tar.gz
下载Boost C++ 1.59.0的开放源码程序库,在后面cmake的时候加上boost的路径
wget https://sourceforge.net/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz/ 

mkdir -p /usr/local/src

cp boost_1_59_0.tar.gz /usr/local/boost/ 

运行环境:centos6.5

版本号:5.7.20
mysql5.5和5.6有很大的不同,需要cmake,解决办法,有两种,在最后,可以把下载的包放到/usr/local/boost目录下,编译安装的时候装上,还有一种是直接编译安装。

第一步:
搭建环境,添加用户和组MySQL,并且不允许登录
groupadd mysql
usradd -r -g mysql -s /sbin/nologin -M mysql
解压:
tar -xf mysql-5.7.20.tar.gz
cd mysql-5.7.20
更新环境准备工作
yum -y install gcc gcc-c++ ncurses ncurses-devel bison libgcrypt perl make cmake
mkdir -p /usr/local/mysql/
mkdir -p /data/mysql
其中data下的属主和属组必须是mysql,date是数据库目录。
ls /usr/local/mysql
chown -R mysql:mysql /data/mysql

4.2cmake配置
cmake . \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \                    MySQL的安装目录basedir
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \               Unix socket 文件路径socket
-DSYSCONFDIR=/etc \                                                系统配置目录
-DDEFAULT_CHARSET=utf8 \                                       使用 utf8 字符
-DDEFAULT_COLLATION=utf8_general_ci \                         校验字符
-DWITH_MYISAM_STORAGE_ENGINE=1 \                              安装 myisam 存储引擎
-DWITH_INNOBASE_STORAGE_ENGINE=1 \                            安装 innodb 存储引擎
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \                            安装 archive 存储引擎
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \                         安装 blackhole 存储引擎
-DENABLED_LOCAL_INFILE=1 \                                      允许从本地导入数据
-DMYSQL_DATADIR=/usr/local/mysql/data \                          数据库存放目录datadir
-DDOWNLOAD_BOOST=1 \
-DMYSQL_TCP_PORT=3306 \
-DENABLE_DOWNLOADS=1
-DWITH_BOOST=/usr/local/boost                             指定下boost目录
(如果在最开始的时候下载boost C++ ,最后要指定boost的目录)


 cmake .\

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql

-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock

-DSYSCONFDIR=/etc

-DDEFAULT_CHARSET=utf8

-DDEFAULT_COLLATION=utf8_general_ci

-DWITH_MYISAM_STORAGE_ENGINE=1

-DWITH_INNOBASE_STORAGE_ENGINE=1

-DWITH_ARCHIVE_STORAGE_ENGINE=1

-DWITH_BLACKHOLE_STORAGE_ENGINE=1

-DENABLED_LOCAL_INFILE=1

-DMYSQL_DATADIR=/data/mysql 

-DDOWNLOAD_BOOST=1

-DMYSQL_TCP_PORT=3306

-DENABLE_DOWNLOADS=1

-DWITH_BOOST=/usr/local/boost


4.3 make&&make install

将安装目录添加到环境变量中:
echo "export PATH=$PATH:/usr/local/mysql/bin">>/etc/profile
source /etc/profile


查看文件错误日志出现这行信息,
说明文件没有初始化,需要删除/data/mysql(数据库目录)下的文档
2017-12-18T15:52:09.477533Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2017-12-18T15:52:09.477899Z 0 [ERROR] unknown variable 'default-file=/etc/my.cnf'
2017-12-18T15:52:09.477915Z 0 [ERROR] Aborting


5 初始化安装MySQL
cd /data/mysql  每次初始化时都要删除该目录下文件(数据库目录)
rm -rf ./*
/usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql \
--basedir=/usr/local/mysql --datadir=/data/mysql

ps aux | grep mysql
会出现如下内容:
Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQ
mysql     27672  0.1  2.5 914052 49200 pts/1    Sl   00:11   0:00 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/data/mysql-error.log --open-files-limit=65535 --pid-file=/usr/local/mysql/mysql.pid --socket=/usr/local/mysql/mysql.sock --port=3306
root      27721  0.0  0.0 103260   876 pts/1    S+   00:15   0:00 grep 3306


6 配置my.cnf
 在启动mysql服务时,会按照一定次序搜索my.cnf,先在/etc目录下找,找不到会搜索
 $basedir/my.cnf 也就是安装目录,新版的配置文件默认位置。将/etc下的my.cnf备份
 否则该文件会影响安装mysql的正确配置,造成无法启动。
 cp my-default.cnf /etc/my.cnf
[client]
port = 3306
socket = /data/mysql/mysql.sock
default-character-set = utf8mb4
[mysqld]
port = 3306
socket = /data/mysql/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
user = mysql
bind-address = 0.0.0.0
server-id = 1                            序列号:本机是1,一般就是master的意思
relay-log=/data/mysql/relay-bin/relay-bin
init-connect = 'SET NAMES utf8mb4'
character-set-server = utf8mb4
#skip-name-resolve                禁止MySQL对外进行DNS解析,使用可以消除MySQL进行解析的时间
                      #如果开启,所以远程主机连接授权都使用IP地址方式,否则MySQL无法正常处理连接请求。
#skip-networking
back_log = 300                            MySQL能有的连接数量
max_connections = 1000
max_connect_errors = 6000    
                      #对同一主机,如果有超过该参数值个数的中断错误连接,主机禁止连接。
                      #解禁,执行flush host
open_files_limit = 65535           #MySQL打开的文件描述符限制,默认最小1024;#当open_files_limit没有被配置的时候,#比较max_connections*5和ulimit -n的值,哪个大用哪个
table_open_cache = 128
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer_size = 4M
thread_cache_size = 8
query_cache_type = 1
query_cache_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4


log_bin = /data/mysql/mysql-bin/mysql-bin        开启log-bin日志,(主从复制)
binlog_format = mixed
expire_logs_days = 30
log_error = /data/mysql/mysql-error.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql-slow.log
performance_schema = 0


explicit_defaults_for_timestamp
#lower_case_table_names = 1
skip-external-locking
default_storage_engine = InnoDB
#default-storage-engine = MyISAM


innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 64M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
interactive_timeout = 28800
wait_timeout = 28800
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

后台启动MySQL

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
正常启动后没有其他内容,回车一下就出来了。

查看MySQL是否启动
ps -aux | grep mysql
将启动的MySQL杀死
kill -9 进程号(两个)
只留下一个就好了:root      80654  0.0  0.0 103256   840 pts/1    S+   08:18   0:00 grep mysql

cd /usr/local/mysql/support-files
将其中的mysql-server复制到启动程序下,并改名为mysqld
cp mysql-server /etc/init.d/
mv mysql-server /etc/init.d/mysqld
chmod 755 mysqld
vi /etc/init.d/mysqld
datadir=/data/mysql
basedir=/usr/local/mysql

启动程序
/etc/init.d/mysqld restart

设置开机自启
chkconfig mysqld on
chkconfig --add mysqld

启动MySQL时出错:
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
[1] 32519
[root@slave1 mysql]# 2017-12-20T12:50:13.972444Z mysqld_safe error:
log-error set to '/usr/local/mysql/mysql-error.log',
however file don't exists. Create writable for user 'mysql'.
解决办法:
echo "" > /usr/local/mysql/mysql-error.log
chown -R mysql:mysql /usr/local/mysql/mysql-error.log
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &



    关于pid的问题
当初始化时会产生一个pid,所以每次读取配置文件都要读取该pid
pid报错时候,用service mysqld restart不能读取,应该用
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
然后从错误的文档中查看。出现的报错信息时间会对不上,找最近的。

    关于cmake时报错
CMake Error at cmake/boost.cmake:76 (MESSAGE):
Call Stack (most recent call first):
  cmake/boost.cmake:228 (COULD_NOT_FIND_BOOST)
 
  CMakeLists.txt:435 (INCLUDE)
 
 解决方法一:
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql
-DMYSQL_DATADIR=/usr/local/mysql/data
-DMYSQL_UNIX_ADDR=/usr/local/mysql/tmp/mysql.sock
-DMYSQL_USER=mysql
-DDEFAULT_CHARSET=utf8
-DDEFAULT_COLLATION=utf8_general_ci
-DENABLED_LOCAL_INFILE=ON
-DWITH_INNOBASE_STORAGE_ENGINE=1
-DWITH_FEDERATED_STORAGE_ENGINE=1
-DWITH_BLACKHOLE_STORAGE_ENGINE=1
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1
-DDOWNLOAD_BOOST=1
-DWITH_BOOST=/usr/local/boost

解决方法二:
下载boost包,放到/usr/local/boost目录下,
cmake后面加选项 -DWITH_BOOST=/usr/local/boost


关于sock问题

2018-01-15T03:10:11.729108Z 0 [ERROR] Could not create unix socket lock file /usr/local/mysql/mysql.sock.lock.
2018-01-15T03:10:11.729117Z 0 [ERROR] Unable to setup unix socket lock file.

尝试给my.cnf备份,然后在将my.cnf中的socket改下路径

mysql启动完后修改密码,首先使mysql无密码登录,然后修改password,5.7版本和其他不同,已经修改password变量,在mysql.user才能查看

首先:

vi /etc/my.cnf

skip-grant-tables

service mysqld restart

mysql -u root -p

>   select * from mysql.user;

>update mysql.user set authentication_string=password("123456") where user=root;

               Host: 192.168.133.%
                  User: root
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: N
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
      password_expired: N
 password_last_changed: 2018-03-25 22:00:31
     password_lifetime: NULL
        account_locked: N

 

 

补充:为用户设定密码

1) mysql>set password for 'username'@'host'=password("password");

2)  #mysqladmin -u username -h host -p password 'password'          这里的password是新密码

3)mysql>update user set authentication_string=password("123456") where user='root' and host='xxx.xxx.xxx.x'

     mysql>flush privileges;

 

图形客户端:

1 phpMyAadmin

2 workbench

3 mysql front

4 Navicat for MySQL

5 Toad

 

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部