mysql主从防火墙端口_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= 3306socket= /data/mysqldata/mysqld.sockdefault-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= 3306basedir= /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= 8max_connections= 200query_cache_limit=1M

query_cache_size=16M

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

expire_logs_days= 10max_binlog_size=100M

character-set-server=utf8

server-id = 1log_bin= /var/log/mysql/mysql-bin.log

sync_binlog=1binlog_format=mixed

lower_case_table_names= 1innodb_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= 3306socket= /data/mysqldata/mysqld.sockdefault-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= 3306basedir= /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= 200query_cache_limit=1M

query_cache_size=16M

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

expire_logs_days= 10max_binlog_size=100M

character-set-server=utf8

lower_case_table_names= 1server-id=2log-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，做已下修改

setenforce0vim/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表示启动正常