mysql主从搭建

1.安装mysql

首先查看是否存在mysql

rpm -qa | grep -i mysql 

yum -y remove mysql-libs*  

依赖：

yum -y install net-tools autoconf

rpm -e mariadb-libs-1:5.5.56-2.el7.x86_64  --nodeps

 

1.1 创建用户

groupadd mysql

useradd -r -g mysql mysql

 

1.2 解压

tar -xvf mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar

 

1.3 安装client

rpm -ivh mysql-community-common-5.7.22-1.el7.x86_64.rpm

rpm -ivh mysql-community-libs-5.7.22-1.el7.x86_64.rpm

rpm -ivh mysql-community-devel-5.7.22-1.el7.x86_64.rpm

rpm -ivh mysql-community-client-5.7.22-1.el7.x86_64.rpm

 

1.4 安装server

rpm -ivh mysql-community-server-5.7.22-1.el7.x86_64.rpm

 

1.5 创建数据目录

mkdir -p /data/mysqldata

chown mysql.mysql /data/mysqldata

 

mkdir /var/log/mysql

chown mysql.mysql /var/log/mysql

 

1.6 修改主服务器配置

vim /etc/my.cnf  

 

[client]

port       = 3306

socket     = /data/mysqldata/mysqld.sock

default-character-set = utf8

 

[mysqld_safe]

socket     =/data/mysqldata/mysqld.sock

nice       = 0

 

[mysqld]

user       = mysql

pid-file   = /data/mysqldata/mysqld.pid

socket     = /data/mysqldata/mysqld.sock

port       = 3306

basedir       = /usr

datadir       = /data/mysqldata

tmpdir     = /tmp

lc-messages-dir   = /usr/share/mysql

skip-external-locking

max_allowed_packet   = 16M

thread_stack      = 192K

thread_cache_size       = 8

max_connections        = 200

query_cache_limit = 1M

query_cache_size        = 16M

log_error = /var/log/mysql/error.log

expire_logs_days  = 10

max_binlog_size         = 100M

character-set-server=utf8

 

server-id = 1

log_bin = /var/log/mysql/mysql-bin.log

sync_binlog=1

binlog_format=mixed

 

lower_case_table_names = 1

innodb_buffer_pool_size=1G

 

 

[mysqldump]

quick

quote-names

max_allowed_packet   = 16M

 

[mysql]

default-character-set=utf8

 

1.7 修改从服务器配置

vim /etc/my.cnf   在【mysqld】添加

 

[client]

port    = 3306

socket      = /data/mysqldata/mysqld.sock

default-character-set = utf8

 

[mysqld_safe]

socket      = /data/mysqldata/mysqld.sock

nice    = 0

 

[mysqld]

user    = mysql

pid-file    = /data/mysqldata/mysqld.pid

socket      = /data/mysqldata/mysqld.sock

port    = 3306

basedir     = /usr

datadir     = /data/mysqldata

tmpdir      = /tmp

lc-messages-dir    = /usr/share/mysql

skip-external-locking

max_allowed_packet = 16M

thread_stack       = 192K

thread_cache_size       = 8

 

#myisam-recover         = BACKUP

max_connections        = 200

 

query_cache_limit  = 1M

query_cache_size        = 16M

 

log_error = /var/log/mysql/error.log

 

expire_logs_days   = 10

max_binlog_size         = 100M

 

character-set-server=utf8

lower_case_table_names = 1

 

server-id=2

log-bin=/var/log/mysql/mysql-bin.log

innodb_buffer_pool_size=1G

 

 

[mysqldump]

quick

quote-names

max_allowed_packet = 16M

 

[mysql]

default-character-set=utf8

 

1.8 初始化数据库

mysqld --initialize

service mysqld start

1.9 防火墙检查

firewall-cmd --state 

#防火墙列表
firewall-cmd --list-all

#防火墙开放3306端口
firewall-cmd --permanent --add-port=3306/tcp

# 防火墙重新加载配置
firewall-cmd –reload 

 

1.10 selinux检查(主)

 

# 输入命令：

getenforce

 

# 如果不是Permissive，做已下修改

setenforce 0

 

vim /etc/selinux/config

 

SELINUX= Permissive

 

1.11 修改密码

grep 'temporary password' /var/log/mysql/error.log

mysql -uroot -p'UA1zL8P9QzNJBcs1'

SET PASSWORD = PASSWORD('xxxx');

show variables like 'datadir';

 

 

2. 主从搭建

2.1 登录主服务器mysql

GRANT REPLICATION SLAVE ON *.* TO 'systop'@'172.31.10.%' IDENTIFIED BY 'systop';

 

FLUSH TABLES WITH READ LOCK;

 

SHOW MASTER STATUS;

记下：

mysql> SHOW MASTER STATUS;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000004 |      693 |              |                  |                   |

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

 

2.2 导出主mysql数据

mysqldump -uroot -p -P3306 --all-databases  --triggers --routines --events >mysqlall.sql

 

2.3 解锁

UNLOCK TABLES;

 

2.4 将数据文件远程拷贝到从服务器

scp mysqlall.sql  192.168.X.X:/tmp/

 

2.5 从服务器导入数据

mysql -uroot -p -h127.0.0.1 -P3306  <  /tmp/mysqlall.sql

 

2.6 从服务器执行

CHANGE MASTER TO MASTER_HOST='172.31.10.13', MASTER_USER='systop',MASTER_PASSWORD='systop',MASTER_LOG_FILE=' mysql-bin.000004',MASTER_LOG_POS=693;

 

2.7 查看同步状态

start slave;

 

show slave status \G;

 

查看:

Slave_IO_Running: Yes

   Slave_SQL_Running: Yes

都显示yes表示启动正常