mysql 数据库主从复制

主库设置

rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000 #此单位是毫秒
log-bin=mysql-bin #打开日志(主机需要打开)
server-id=1 #服务器id
log-bin-index=mysql-bin.index
sync_binlog=1
#给从机同步的库，可以多个
binlog-do-db=tp #从库的数据库名称
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
expire_logs_days=1

GRANT REPLICATION SLAVE ON . TO ‘root’@‘192.168.182.129’ IDENTIFIED BY ‘root’; #主数据库授权同步账户
FLUSH PRIVILEGES; #刷新权限

从库设置

rpl_semi_sync_slave_enabled=1
server-id=2 #服务器id

要从主机同步的库

replicate-do-db=tp #主库的数据库名

CHANGE MASTER TO MASTER_HOST=‘192.168.182.128’,MASTER_USER=‘root’, MASTER_PASSWORD=‘root’,MASTER_LOG_FILE=‘mysql-bin.000001’,MASTER_LOG_POS=603;
start slave; #开启SLAVE同步
show slave status \G; #查看下slave状态

注意：MASTER_LOG_FILE=’mysql-bin.000001’,MASTER_LOG_POS=603是通过前面的主数据库SHOW MASTER STATUS;得到

stop slave; #停止SLAVE同步
GRANT REPLICATION SLAVE ON . TO ‘root’@‘192.168.182.128’ IDENTIFIED BY ‘root’;# 撤销已经赋予给MySQL同步账户的权限

./configure --prefix=/www/soft/php/73 --with-config-file-path=/www/soft/php/73/etc --enable-fpm --with-fpm-user=www --with-fpm-group=www --enable-mysqlnd --with-mysqli=mysqlnd --with-pdo-mysql=mysqlnd --with-iconv-dir --with-freetype-dir --with-jpeg-dir --with-png-dir --with-zlib --with-libxml-dir --enable-xml --disable-rpath --enable-bcmath --enable-shmop --enable-sysvsem --enable-inline-optimization --with-curl --enable-mbregex --enable-mbstring --enable-intl --enable-ftp --with-gd --with-openssl --with-mhash --enable-pcntl --enable-sockets --with-xmlrpc --enable-soap --with-gettext --disable-fileinfo --enable-opcache --with-sodium=/usr/local/libsodium --with-webp-dir