linux mysql 主从配置_linux上mysql配置 主从服务器

环境准备：

linux版本：centos 7.2

mysql版本：mysql-5.7.27-linux-glibc2.12-x86_64

部署节点：主从各一台节点

2、解压

tar -zxvf mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz

ln -s /usr/share/doc/mysql-5.7.27-linux-glibc2.12-x86_64 /usr/local/mysql

3、创建数据目录

mkdir /usr/local/mysql/data

4、创建mysql用户和组

groupadd -r mysql

useradd -r -g mysql mysql

chown -R mysql:mysql /usr/local/mysql

5、添加环境变量

vim /etc/profile

export PATH=$PATH:/usr/local/mysql/bin

source /etc/profile

6、配置my.cnf

[mysqld]

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

character-set-server=utf8

default_storage_engine = InnoDB

max_allowed_packet=1024M

port=3306

user=mysql

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

symbolic-links=0

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

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

symbolic-links=0

server-id=1

log-bin=master-bin

log-bin-index=master-bin.index

7、初始化数据库

mysqld --initialize --explicit_defaults_for_timestamp --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

注：如果重复执行初始化，必须先清空 /usr/local/mysql/data 目录

8、启动mysql服务

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

service mysqld start

9、获取初始密码登陆数据库

执行：cat /var/log/mysqld.log | grep 'temporary password'

2019-08-26T06:47:08.406329Z 1 [Note] A temporary password is generated for root@localhost: dP!Ut;hsM1%M

其中：dP!Ut;hsM1%M 就是初始密码，如果日志中没有查找到初始密码可以执行以下操作

vim /etc/my.cnf 添加：skip-grant-tables 取消密码验证，重启服务：service mysqld restart

mysql -u root -p

免密码登陆后执行SQL修改密码：

set password for 'root'@'localhost'=password('password');

flush privileges;

10、主库创建用于连接的用户

grant replication slave on *.* to 'root'@'localhost' identified by '***';

flush privileges;

11、部署从数据库

从数据库与主数据库安装步骤一致，其中 /etc/my.cnf配置如下：

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

port=3306

user=mysql

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

symbolic-links=0

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

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

symbolic-links=0

server-id=2

relay-log-index=slave-relay-bin.index

relay-log=slave-relay-bin

log-bin=relay-bin

read_only=1

12、启动主从同步

登陆主数据库，执行 ：show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000001 | 154 | | | |

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

登陆从数据库

change master to master_host='master_ ip' ,master_port=3306,master_user='repl',master_password='xxx',master_log_file='master-bin.000001,master_log_pos=154;

start slave;

查看从数据库信息：show slave status \G;

13、测试同步情况

登陆主数据库

create database d_test;

use d_test;

create table t_test()

CREATE TABLE `t_test` (

`id` int NOT NULL AUTO_INCREMENT ,

`content` varchar(20) NULL ,

PRIMARY KEY (`id`)

);

INSERT INTO `t_test` (`content`) VALUES ('test1'),('test2'),('test3'),('test4');

select * from t_test;

登陆从数据库

use d_test;

select * from t_test;

对比查询结果，确认是否同步成功！