CentOS7通过yum安装Mysql5.7+修改默认密码+远程登录+主从复制+keepalive高可用

CentOS7通过yum安装Mysql5.7+修改默认密码+远程登录+主从复制+keepalive高可用

环境设置

mysql-mater 192.168.20.51

mysql-slave 192.168.20.52

vip：192.168.20.91

vim /etc/hosts

192.168.10.52 sqla
192.168.10.51 sqlb

 

1、配置yum源

# 下载mysql源安装包

[root@localhost ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm

# 安装mysql源

[root@localhost ~]# yum localinstall mysql57-community-release-el7-8.noarch.rpm

检查mysql源是否安装成功

[root@localhost ~]# yum repolist enabled | grep "mysql.*-community.*"

2、安装mysql

[root@localhost ~]#yum install mysql-community-server

3、启动MySQL服务

[root@localhost ~]# systemctl start mysqld.service

[root@localhost ~]# systemctl status mysqld.service

4、开机启动

[root@localhost ~]# systemctl enable mysqld

[root@localhost ~]# systemctl daemon-reload

[root@localhost ~]# grep 'temporary password' /var/log/mysqld.log

2018-09-10T03:43:48.298022Z 1 [Note] A temporary password is generated for root@localhost: gVxip6#KC!2w

5、修改root默认密码

mysql安装完成之后，在/var/log/mysqld.log文件中给root生成了一个默认密码。 
查看默认密码

 [root@localhost ~]# grep 'temporary password' /var/log/mysqld.log

2018-09-10T03:43:48.298022Z 1 [Note] A temporary password is generated for root@localhost: gVxip6#KC!2w

其中 gVxip6#KC!2w 就是我的默认密码

HVu8keo+x>wo

用默认密码登录

shell> mysql -uroot -p

mysql> Enter password: gVxip6#KC!2w

#设置密码策略，可以设置123456

set global validate_password_policy=0;
set global validate_password_length=4;

修改默认密码:

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

# 上面的root是你的新密码

6、允许root远程登录

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.20.%' IDENTIFIED BY '密码' WITH GRANT OPTION;mysql>flush privileges;

第一句中”%”表示任何主机都可以远程登录到该服务器上访问。如果要限制只有某台机器可以访问，将其换成相应的IP即可，如： 
GRANT ALL PRIVILEGES ON . TO root@”192.168.20.%” IDENTIFIED BY “密码”; 
123@asdf是登录密码。 
第二句表示从mysql数据库的grant表中重新加载权限数据。因为MySQL把权限都放在了cache中，所以在做完更改后需要重新加载。

7、修改字符集编码 UTF-8

 

查看当前的字符编码

show variables like 'char%'；

检测是否都是utf-8（filesystem除外），如果不是就得改，例如这个character_set_server一般是拉丁编码 
修改编码

//停数据库

systemctl stop mysqld

//进入 my.cnf 文件，一般是在etc路径下

vim /etc/my.cnf

//加入要修改的字符集 修改完:wq退出

在[mysqld]下追加：

character-set-server=utf8

//重启数据库

systemctl start mysqld

 

8、生产mysql主服务具体配置：

 

[client]

port = 3306

#socket = /tmp/mysql.sock

socket=/var/lib/mysql/mysql.sock

default-character-set = utf8mb4

 

[mysqld]

port = 3306

#socket = /tmp/mysql.sock

socket=/var/lib/mysql/mysql.sock

 

 

#basedir = /usr/local/mysql

#datadir = /var/mysql/data

#pid-file = /var/mysql/data/mysql.pid

datadir=/var/lib/mysql

pid-file=/var/run/mysqld/mysqld.pid

user = mysql

bind-address = 0.0.0.0

 

init-connect = 'SET NAMES utf8mb4'

character-set-server = utf8mb4

explicit_defaults_for_timestamp = 1

lower_case_table_names=1

skip-name-resolve

#skip-networking

back_log = 300

 

max_connections = 1000

max_connect_errors = 600000

open_files_limit = 65535

#table-definition-cache = 1024

#table_open_cache = 2048

max_allowed_packet = 16M

max_heap_table_size = 32M

tmp_table_size = 32M

 

read_buffer_size = 16M

read_rnd_buffer_size = 32M

sort_buffer_size = 8M

join_buffer_size = 8M

key_buffer_size = 8M

 

thread_cache_size = 512

 

query_cache_type = 0

query_cache_size = 0

#query_cache_limit = 2M

 

ft_min_word_len = 4

 

log_bin = mysql-bin

binlog_format = ROW

expire_logs_days = 7

server-id = 12

log-slave-updates

slave-skip-errors=all

sync_binlog=1

auto_increment_increment=2

auto_increment_offset=1

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=16

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_recovery=ON

 

 

#log_error = /var/mysql/data/mysql-error.log

log-error=/var/log/mysqld.log

slow_query_log = 1

long_query_time = 1

slow_query_log_file = /var/mysql/data/mysql-slow.log

 

performance_schema = 0

 

#lower_case_table_names = 1

 

skip-external-locking

 

default_storage_engine = InnoDB

innodb-flush-method            = O_DIRECT

innodb-log-files-in-group      = 2

innodb-log-file-size           = 128M

innodb-flush-log-at-trx-commit = 1

innodb-file-per-table          = 1

innodb-buffer-pool-size        = 18G

 

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

·

9、生产mysql从服务具体配置：

 

[client]

port = 3306

#socket = /tmp/mysql.sock

socket=/var/lib/mysql/mysql.sock

default-character-set = utf8mb4

 

[mysqld]

port = 3306

#socket = /tmp/mysql.sock

socket=/var/lib/mysql/mysql.sock

default_password_lifetime=0

#basedir = /usr/local/mysql

#datadir = /var/mysql/data

datadir=/var/lib/mysql

pid-file=/var/run/mysqld/mysqld.pid

#pid-file = /var/mysql/data/mysql.pid

user = mysql

bind-address = 0.0.0.0

 

init-connect = 'SET NAMES utf8mb4'

character-set-server = utf8mb4

explicit_defaults_for_timestamp = 1

lower_case_table_names=1

skip-name-resolve

range_optimizer_max_mem_size=0

#skip-networking

back_log = 300

 

max_connections = 1000

max_connect_errors = 600000

open_files_limit = 65535

#table_open_cache = 2048

#table-definition-cache = 1024

max_allowed_packet = 16M

max_heap_table_size = 32M

tmp_table_size = 32M

 

read_buffer_size = 16M

read_rnd_buffer_size = 32M

sort_buffer_size = 8M

join_buffer_size = 8M

key_buffer_size = 8M

 

#thread_cache_size = 30

 

query_cache_type = 0

query_cache_size = 0

#query_cache_limit = 2M

 

ft_min_word_len = 4

 

log_bin = mysql-bin

binlog_format = ROW

expire_logs_days = 7

server-id = 11

log-slave-updates

slave-skip-errors=all

sync_binlog=1

auto_increment_increment=1

auto_increment_offset=1

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=16

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_recovery=ON

 

log-error=/var/log/mysqld.log

#log_error = /var/mysql/data/mysql-error.log

slow_query_log = 1

long_query_time = 1

slow_query_log_file = /var/mysql/data/mysql-slow.log

 

performance_schema = 0

 

#lower_case_table_names = 1

 

skip-external-locking

 

default_storage_engine = InnoDB

innodb-flush-method            = O_DIRECT

innodb-log-files-in-group      = 2

innodb-log-file-size           = 128M

innodb-flush-log-at-trx-commit = 1

innodb-file-per-table          = 1

innodb-buffer-pool-size        = 6G

 

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

 

10、设置mysql主从复制

 

10.1 修改mysql配置

找到主数据库的配置文件my.cnf(或者my.ini)，我的在/etc/mysql/my.cnf,在[mysqld]部分插入如下两行：

[mysqld]

log-bin=mysql-bin #开启二进制日志

server-id=11 #设置server-id

10.2.重启mysql，创建用于同步的用户账号

打开mysql会话shell>mysql -uroot -p

创建用户并授权：用户：replication密码：用户密码

mysql> GRANT REPLICATION SLAVE ON *.* to 'replication'@'192.168.20.52' identified by '密码';

mysql>flush privileges;   #刷新权限

10.3.查看master状态，记录二进制文件名(mysql-bin.000004)和位置(730)：

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000004 |      730 |              |                  |                   |

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

1 row in set (0.00 sec)

#查询权限

select * from mysql.user;

 

10.4、配置从服务器Slave：

mysql>change master to master_host='192.168.20.51',master_user='replication ',master_password='密码',master_log_file='mysql-bin.000004',master_log_pos=730;  

//注意不要断开，308数字前后无单引号。

Mysql>start slave;    //启动从服务器复制功能

mysql> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.20.51

                  Master_User: replication

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000004

          Read_Master_Log_Pos: 887

               Relay_Log_File: localhost-relay-bin.000002

                Relay_Log_Pos: 477

        Relay_Master_Log_File: mysql-bin.000004

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

10.5、主从服务器测试：

主服务器Mysql，建立数据库，并在这个库中建表插入一条数据：

  mysql> create database hi_db;
  Query OK, 1 row affected (0.00 sec)

  mysql> use hi_db;
  Database changed

  mysql>  create table hi_tb(id int(3),name char(10));
  Query OK, 0 rows affected (0.00 sec)
 
  mysql> insert into hi_tb values(001,'bobu');
  Query OK, 1 row affected (0.00 sec)

  mysql> show databases;
   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | hi_db                |
   | mysql                |
   | test                 |
   +--------------------+
   4 rows in set (0.00 sec)

从服务器Mysql查询：

   mysql> show databases;

   +--------------------+
   | Database               |
   +--------------------+
   | information_schema |
   | hi_db                 |       //I'M here，大家看到了吧
   | mysql                 |
   | test          |

   +--------------------+
   4 rows in set (0.00 sec)

   mysql> use hi_db
   Database changed
   mysql> select * from hi_tb;           //查看主服务器上新增的具体数据
   +------+------+
   | id   | name |
   +------+------+
   |    1 | bobu |
   +------+------+
   1 row in set (0.00 sec)

 

11、安装keepalived，并配置

 

11.1主从安装

keepalived 安装
#安装keepavlived
yum -y install keepalived

#设置开机启动
systemctl enable keepalived.service

#启动服务
service keepalived start

11.2 主keepalived配置

! Configuration File for keepalived

 

global_defs {

   notification_email {

     acassen@firewall.loc

     failover@firewall.loc

     sysadmin@firewall.loc

   }

   notification_email_from Alexandre.Cassen@firewall.loc

   smtp_server 192.168.200.1

   smtp_connect_timeout 30

   router_id LVS_DEVEL

}

 

vrrp_script check_mysql {

    script "/etc/keepalived/scripts/mysql_check.sh"

    interval 2

}

 

vrrp_sync_group VG1 {

    group {

        VI_1

    }

}

 

vrrp_instance VI_1 {

    state MASTER

    interface em1

    virtual_router_id 88

    priority 100

    advert_int 1

    authentication {

        auth_type PASS

        auth_pass 1111

    }

    track_script {

        check_mysql

    }

 

    virtual_ipaddress {

        192.168.20.91 label em1

    }

 

    notify_master /etc/keepalived/scripts/master.sh

    notify_backup /etc/keepalived/scripts/backup.sh

    notify_stop /etc/keepalived/scripts/stop.sh

}

11.3 从keepalived配置

! Configuration File for keepalived

 

global_defs {

   notification_email {

     acassen@firewall.loc

     failover@firewall.loc

     sysadmin@firewall.loc

   }

   notification_email_from Alexandre.Cassen@firewall.loc

   smtp_server 192.168.200.1

   smtp_connect_timeout 30

   router_id LVS_DEVEL

}

 

vrrp_script check_mysql {

    script "/etc/keepalived/scripts/mysql_check.sh"

    interval 2

}

 

vrrp_sync_group VG1 {

    group {

        VI_1

    }

}

 

vrrp_instance VI_1 {

    state BACKUP

    interface em1

    virtual_router_id 88

    priority 80 

    advert_int 1

#    nopreempt #不进行抢占操作

    authentication {

        auth_type PASS

        auth_pass 1111

    }

    track_script {

        check_mysql

    }

 

    virtual_ipaddress {

        192.168.20.91 label em1

    }

 

    notify_master /etc/keepalived/scripts/master.sh

    notify_backup /etc/keepalived/scripts/backup.sh

    notify_stop /etc/keepalived/scripts/stop.sh

}

注意：一定一定要给/etc/keepalived/scripts权限