MySQL primary-replica (master-slave) 配置

By admin, 20 二月, 2020

https://linuxize.com/post/how-to-configure-mysql-master-slave-replication-on-debian-10/

https://www.howtoforge.com/one_machine_mysql_replication

如何编译和在BUILD目录里运行Mariadb:

 

具体步骤如下:

1. 下载系统相同版本的Mariadb,并编译。也可以使用系统自带的版本,因为默认的配置文件是全局的,最终都是要通过改写命令行参数来指定不同的数据目录。使用系统自带版本可以获得同步更新,减少兼容性问题。

BUILD/compile-pentium64

2. 创建一个独立的副本目录,可以创建多个来分担计算量(假设一个MySQL进程只能用尽一个CPU核,在多核CPU里这样做可能是有用的,但也要考虑内存分配)

mkdir -p /var/www/mariadb_replicate1/var/lib/mysql

3. 初始化数据库

mariadb-10.1-10.1.44# ./scripts/mysql_install_db --srcdir=. --datadir=/var/www/mariadb_replicate1/var/lib/mysql

4. 配置主服务

Default options are read from the following files in the given order:

/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 

主服务器包含如下配置:

[mysqld]
port = 3306
socket = /usr/run/mysqld/mysql.sock
bind-address            = 127.0.0.1 # 不在同一台服务器上需要修改为具体局域网IP

# binary logging is required for replication
log_bin                 = /var/log/mysql/mysql-bin.log

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1

5. 配置从服务

 

# mkdir -p /var/www/mariadb_replicate1/etc/mysql

# mkdir -p /var/www/mariadb_replicate1/var/run/mysqld

# mkdir -p /var/www/mariadb_replicate1/tmp

# mkdir -p /var/www/mariadb_replicate1/var/log/mysql/

# cp -r /etc/mysql/mariadb.conf.d /var/www/mariadb_replicate1/etc/mysql/

# cp -r /etc/mysql/conf.d /var/www/mariadb_replicate1/etc/mysql/

# chown -R mysql:mysql /var/www/mariadb_replicate1

 

# vi /var/www/mariadb_replicate1/etc/mysql/my.cnf

[client-server]

!includedir /var/www/mariadb_replicate1/etc/mysql/conf.d/

!includedir /var/www/mariadb_replicate1/etc/mysql/mariadb.conf.d/

 

# vi /var/www/mariadb_replicate1/etc/mysql/mariadb.conf.d/50-server.conf

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

socket          = /var/www/mariadb_replicate1/var/run/mysqld/mysqld.sock

port            = 3307

basedir         = /usr

datadir         = /var/www/mariadb_replicate1/var/lib/mysql

tmpdir          = /var/www/mariadb_replicate1/tmp

bind-address            = 127.0.0.1 # 不在同一台机器的话要改

# slave_skip_errors = 1062 # 如果我们在服务器运行之后才配置replicate,那么就会遇到很多Duplicate entry错误,这个配置可以忽略这一系列错误

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

server-id               = 2

#log_bin

 

# vi /var/www/mariadb_replicate1/etc/mysql/mariadb.conf.d/50-mysql_safe.cnf

socket          = /var/www/mariadb_replicate1/var/run/mysqld/mysqld.sock

 

#vi /var/www/mariadb_replicate1/etc/mysql/mariadb.conf.d/50-client.cnf

socket          = /var/www/mariadb_replicate1/var/run/mysqld/mysqld.sock

 

6. 启动服务

 

# service mysql restart

# mysqld --defaults-file=/var/www/mariadb_replicate1/etc/mysql/my.cnf &;

 

7. 在主服务里做账号授权

# mysql -p

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO replicate1@localhost identified by 'Bh20200207';

8. 在从服务里配置主服务连接

#mysql --socket /var/www/mariadb_replicate1/var/run/mysqld/mysqld.sock

mysql>CHANGE MASTER TO MASTER_HOST='localhost',MASTER_USER='replicate1',MASTER_PASSWORD='pwd',MASTER_PORT=3306,MASTER_CONNECT_RETRY=30;

mysql>start slave;

mysql>show slave status;

# 设置root密码

mysql>grant all privileges on *.* to root@localhost identified by '<password>';

最后slave status的bin文件和position信息应该和主服务的show master status;对应。

 

标签

评论

Restricted HTML

  • 允许的HTML标签:<a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id> <img src>
  • 自动断行和分段。
  • 网页和电子邮件地址自动转换为链接。
验证码
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.
请输入"Drupal10"