mysql 9.2 主从复制配置

官网参考文档：

https://dev.mysql.com/doc/refman/9.2/en/replication-configuration.html

注意：主库和从库 server_id 一定不能一样

主库配置

create user 'repl'@'%' identified by 'replpasswd';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

如果修改密码用下面密码修改

ALTER USER 'repl'@'%' IDENTIFIED by 'newpasswd';

SHOW BINARY LOG STATUS\G

从库配置

CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.1.x',
SOURCE_USER='repl',
SOURCE_PASSWORD='replpasswd',
SOURCE_LOG_FILE='mysql-bin.000001',
SOURCE_LOG_POS=0;

START REPLICA;
STOP REPLICA;

show replica status\G

这里面涉及到一个复制账号的问题

在MySQL8.0之前，身份验证的插件是mysql_native_password，在MySQL 8.0或者以上版本，caching_sha2_password 是默认的身份验证插件，安全性更高。

从库报错

Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

参考文章：

https://www.modb.pro/db/29919

最终解决方案：

在从库运行下面命令：

mysql -u repl -p'replpasswd' -h 192.168.1.x -P3306 --get-server-public-key

还有一种不推荐的方式

[mysqld]

default_authentication_plugin=mysql_native_password #默认使用mysql_native_password插件认证

 

 

附赠一份源码安装脚本

vim  my9.cnf 

 

#https://dev.mysql.com/doc/refman/9.2/en/server-configuration.html 参考文档
[client]
port = 3306
host = localhost
socket = /data/mysql1/mysql.sock
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
[mysqld1]
character-set-server=utf8mb4 #有现在utf8mb4默认编码，提示让修改成这个，优化了性能，具体没有测试
bind-address = 0.0.0.0
port = 3306
server-id = 1
innodb_buffer_pool_size = 3G
#default_authentication_plugin=mysql_native_password #默认使用mysql_native_password插件认证，MySQL 8.0改了默认加密方式为caching_sha2_password这个比较严格
max_connections = 3000
max_connect_errors = 100
connect-timeout = 5
open_files_limit = 65535
back_log = 512 #MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中
max_allowed_packet = 64M
slow-query-log = 1
slow-query-log-file = /data/mysql1/slow.log
#long-query-time = 1
#log_slow_admin_statements=1 #参数设置为1，而这个参数只在5.6.11后支持
#min_examined_row_limit=N #表示只有返回条数大于N才记录到慢查询
#log_queries_not_using_indexes #记录没有索引的SQL
#log_throttle_queries_not_using_indexes=N #为N后表示1分钟内该SQL最多记录N条
#log_slow_slave_statements #在从库开启慢查询语句
#skip-host-cache
skip-name-resolve
log-bin = mysql-bin
log-bin-index = mysql-bin.index
#binlog_expire_logs_seconds=25200 #expire-logs-days参数已经没有了，取而代之的是binlog_expire_logs_seconds，单位是秒
log_timestamps=SYSTEM
basedir = /usr/local/mysql
datadir = /data/mysql1
socket = /data/mysql1/mysql.sock
log_error_verbosity=1
log-error = /data/mysql1/mysql.err
pid-file = /data/mysql1/mysql.pid
sql-mode="NO_ENGINE_SUBSTITUTION" #NO_AUTO_CREATE_USER没有了
join_buffer_size = 256M
sort_buffer_size = 4M
read_rnd_buffer_size = 4M

 

vim mysql9.sh

#!/bin/bash

patch=/usr/local/src

mysql_tar=mysql-9.2.0

if [ `whoami` != "root" ];then

    echo "Installtion needs root user!"

    exit 1

fi

if (netstat -nulpt | grep mysqld)

then

    echo "mysqld installed"

    exit 1

else

    echo "install packages"

fi

function sys_init(){

    dnf  install  epel-release  perl cmake ncurses-devel gcc-toolset-13-gcc gcc-toolset-13-gcc-c++ gcc-toolset-13-binutils bison openssl-devel \

    gcc-toolset-13-annobin-annocheck gcc-toolset-13-annobin-plugin-gcc libtirpc-devel \

   https://yum.oracle.com/repo/OracleLinux/OL8/codeready/builder/x86_64/getPackage/rpcgen-1.3.1-4.el8.x86_64.rpm -y

    if (grep mysql /etc/passwd)

    then

        echo "start install MYSQL"

    else

        useradd -s /sbin/nologin/ mysql

    fi

}

function install_mysql() {

    cd ${patch}/

    if (! ls | grep ${mysql_tar}.tar.gz)

    then

        wget https://cdn.mysql.com//Downloads/MySQL-9.2/${mysql_tar}.tar.gz

    fi

    rm -rf ${mysql_tar}

    tar -zxf ${mysql_tar}.tar.gz

    cd ${mysql_tar}

    mkdir -p build

    cd build

    cmake .. -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \

    -DWITH_DEBUG=OFF \

    -DENABLED_PROFILING=ON \

    -DWITH_EXTRA_CHARSETS=all \

    -DWITH_INNOBASE_STORAGE_ENGINE=ON \

    -DWITH_FEDERATED_STORAGE_ENGINE=ON \

    -DWITH_ARCHIVE_STORAGE_ENGINE=ON \

    -DWITH_EMBEDDED_SERVER=OFF \

    -DWITH_BLACKHOLE_STORAGE_ENGINE=ON \

    -DWITH_UNIT_TESTS=OFF \

    -DENABLE_DTRACE=OFF \

    -DWITH_BOOST=/usr/local/src \

    -DDOWNLOAD_BOOST=1 \

    -DMYSQL_DATADIR=/data/mysql1

    make -j

    make install

}

function start_mysql() {

    echo "start install mysql"

    /usr/local/mysql/bin/mysqld \

    --initialize-insecure \

    --user=mysql \

    --basedir=/usr/local/mysql/ \

    --datadir=/data/mysql1/

    #刚才my9.cnf  复制过来就行

    mv  -f my9.cnf  /etc/my.cnf

    if (! grep /usr/local/mysql/lib /etc/ld.so.conf)

    then

        echo "/usr/local/mysql/lib" >> /etc/ld.so.conf

        ldconfig

    fi

    if (! grep /usr/local/mysql/bin /etc/profile)

    then

        echo 'export PATH="$PATH:/usr/local/mysql/bin"' >> /etc/profile

    fi

   

    if [ ! -f /usr/bin/my_print_defaults ]

    then

        cp /usr/local/mysql/bin/my_print_defaults /usr/bin/my_print_defaults

    fi

    source /etc/profile

    /usr/local/mysql/bin/mysqld_multi start

}

sys_init

install_mysql

if [[ $?==0 ]]

then

start_mysql

fi