mysql8 主从搭建
主：192.168.10.2 从：192.168.10.3
主：
1.登录mysql，授权账号，让从数据库可以进行复制。
mysql CREATE USER 'repl'@'192.168.10.3' IDENTIFIED BY 'mysql23'; grant replication slave on *.* to 'repl'@'192.168.10.3'; mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000004 | 156 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
从：
mysql stop slave; change master to master_host='192.168.10.2',master_user='repl',master_password='mysql23',master_log_file=' mysql-bin.000004',master_log_pos=156; start slave; show slave status \G;
注：server-id主从库一定不一样。
追加。一键部署mysql主从，脚本分享，和使用示例
1.my.cnf配置文件，可根据需要进行调整。
[client] port = 3307 socket = /tmp/mysql_test3307.sock prompt=(\\u@\\h) [\\d]>\\_ # The MySQL server #########Basic################## [mysqld] ##bind_address = 127.0.0.1 server-id = 413307 port = 3307 user = mysql basedir = /usr/local/mysql datadir = /data/mysql/test3307/data tmpdir = /data/mysql/test3307/tmp socket = /tmp/mysql_test3307.sock skip-external-locking skip-name-resolve sql_mode = '' default_authentication_plugin = mysql_native_password ### Percona ### #extra_port = 13307 #extra_max_connections = 100 ###skip-networking default-storage-engine = INNODB wait_timeout = 3600 connect_timeout = 20 interactive_timeout = 3600 back_log = 500 #event_scheduler = ON open_files_limit = 65535 #thread_handling = pool-of-threads lower_case_table_names = 1 ###### binlog ###### log-bin = /data/mysql/test3307/binlog/mysql-bin binlog_format = ROW binlog_checksum = NONE sync_binlog = 1 gtid_mode = on enforce_gtid_consistency = on log_slave_updates = 1 max_binlog_size = 128M binlog_cache_size = 1M #########replication############# relay-log = /data/mysql/test3307/relaylog/relay-log slave-net-timeout = 10 #rpl_semi_sync_master_enabled = 1 #rpl_semi_sync_master_wait_no_slave = 1 #rpl_semi_sync_master_timeout = 1000 #rpl_semi_sync_slave_enabled = 1 #skip-slave-start log_slave_updates = 1 relay_log_recovery = 1 #slave_skip_errors = 1062 read_only = 0 ###### Mgr config ###### loose-transaction_write_set_extraction = XXHASH64 slave_parallel_type = LOGICAL_CLOCK slave_parallel_workers = 24 slave_preserve_commit_order = ON ################################## #########slow log############# slow_query_log = 1 slow_query_log_file = /data/mysql/test3307/log/mysql-slow.log #log-slow-verbosity = query_plan,explain long_query_time = 5 #########error log############# log-error = /data/mysql/test3307/log/error.log log_timestamps = system #######per_thread_buffers############ max_connections=4100 max_user_connections=4000 max_connect_errors=100000000 max_allowed_packet = 256M table_open_cache = 6144 table_definition_cache = 4096 sort_buffer_size = 128K read_buffer_size = 128K read_rnd_buffer_size = 128k join_buffer_size = 128K tmp_table_size = 64M max_heap_table_size = 64M bulk_insert_buffer_size = 32M thread_cache_size = 64 #thread_concurrency = 32 thread_stack = 256K ##MyISAM## key_buffer_size = 64M myisam_sort_buffer_size = 8M concurrent_insert=2 low_priority_updates=1 myisam_recover_options=BACKUP,FORCE ######### InnoDB ############# innodb_numa_interleave = 1 innodb_sort_buffer_size = 32M innodb_rollback_on_timeout = 1 innodb_data_file_path = ibdata1:10M:autoextend innodb_buffer_pool_dump_at_shutdown = 1 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_size = 300M innodb_buffer_pool_instances = 16 innodb_log_file_size = 512M innodb_log_buffer_size = 64M innodb_log_files_in_group = 3 innodb_flush_log_at_trx_commit = 2 innodb_lock_wait_timeout = 10 innodb_sync_spin_loops = 40 innodb_max_dirty_pages_pct = 95 innodb_thread_concurrency = 0 innodb_thread_sleep_delay = 500 innodb_concurrency_tickets = 1000 log_bin_trust_function_creators = 1 innodb_flush_method = O_DIRECT innodb_file_per_table = 1 innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb_io_capacity = 800 innodb_purge_threads = 16 innodb_purge_batch_size = 32 innodb_old_blocks_time = 1000 innodb_change_buffering = all transaction_isolation = READ-COMMITTED innodb_stats_on_metadata = 0 ####### NEW ######## ##innodb_kill_idle_transaction = 5 ###### SSD ######### #innodb_flush_neighbors = 0 #innodb_log_block_size = 4096 #################### [mysqldump] quick max_allowed_packet = 128M [mysql] no-auto-rehash prompt=(\\u@\\h) [\\d]>\\_ [myisamchk] key_buffer_size = 64M sort_buffer_size = 256k read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [mysqld_safe] open-files-limit = 65535 #flush_caches = 1 #numa_interleave = 1
2.执行脚本
#!/bin/bash ################################################################################### # 描述:安装mysql和配置主从复制 #created by zyt # 用法: #1. 将mysql-8.0.23-linux-glibc2.12-x86_64.tar.xz、my_test.cnf和install.sh文件拷贝至某一个目录 #2. 根据需要修改配置参数： #3. sh install.sh 安装mysql服务器 #4. sh install.sh repl 配置主从 echo "正在安装MySQL软件......." useradd mysql ####################配置参数 mysql8_version=mysql-8.0.23-linux-glibc2.12-x86_64.tar.xz mysql8_version_dir=mysql-8.0.23-linux-glibc2.12-x86_64 #同步复制用户 repl_user=repl repl_passwd=sysrepl root_passwd=123456 ####################主从复制配置########################## if [ "$1" = "repl" ] then while true do read -t 30 -p "输入你数据库的name: " dbname read -t 30 -p "输入你数据库的port: " yourport read -t 30 -p "输入你的主库IP: " master_ip read -t 30 -p "输入你的主库端口号: " master_port if [[ -z $master_ip || -z $master_port ]] then continue else echo "" echo "主库IP是： $master_ip" echo "主库端口号是： $master_port" break fi done /usr/local/mysql/bin/mysql -u'root' -h'localhost' -p$root_passwd -P"$yourport" -S /tmp/mysql_$dbname.sock --connect-expired-password -e "stop slave;CHANGE MASTER TO MASTER_HOST='$master_ip',MASTER_USER='repl',MASTER_PASSWORD='$repl_passwd',MASTER_PORT=$master_port,MASTER_AUTO_POSITION = 1; START SLAVE;" echo "MySQL主从复制同步已经初始化完毕。" exit 0 fi ####################环境检查和准备 ps aux | grep 'mysql' | grep -v 'grep' | grep -v 'bash' if [ $? -eq 0 ] then echo "MySQL进程已经启动，无需二次安装。" exit 0 fi if [ ! -d /usr/local/${mysql8_version_dir} ] then yum install -y libaio tar -Jxvf ${mysql8_version} -C /usr/local/ ln -s /usr/local/${mysql8_version_dir} /usr/local/mysql chown -R mysql.mysql /usr/local/mysql else ln -s /usr/local/${mysql8_version_dir} /usr/local/mysql chown -R mysql.mysql /usr/local/mysql fi ####################数据库参数配置 while true do read -t 30 -p "输入你的数据库名: " dbname read -t 30 -p "输入你的数据库端口号: " dbport read -t 30 -p "输入MySQL serverId: " serverId read -t 30 -p "输入innodb_buffer_pool_size大小，单位M: " innodb_bp_size if [[ -z $dbname || -z $dbport || -z $serverId || -z $innodb_bp_size ]] then continue else echo "数据库名字是： $dbname" echo "数据库端口是： $dbport" echo "MySQL serverId： $serverId" echo "BP大小是： $innodb_bp_size MB" break fi done sed "s/test/$dbname/g;s/3306/$dbport/;s/41/$serverId/;/innodb_buffer_pool_size/s/1/$innodb_bp_size/" my_test.cnf > /etc/my_$dbname.cnf DATA_DIR=/data/mysql/$dbname [ ! -d $DATA_DIR ] && mkdir -p $DATA_DIR/{data,binlog,relaylog,tmp,slowlog,log}; touch $DATA_DIR/log/error.log; chown -R mysql.mysql /data/mysql/ ####################数据库初始化 if [ `ls -A $DATA_DIR/data/ | wc -w` -eq 0 ] then cd /usr/local/mysql echo "" echo "初始化MySQL数据目录......" echo "" bin/mysqld --defaults-file=/etc/my_$dbname.cnf --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/$dbname/data sleep 2 bin/mysqld_safe --defaults-file=/etc/my_$dbname.cnf --user=mysql & fi while true do netstat -ntlp | grep $dbport if [ $? -eq 1 ] then echo "MySQL启动中，稍等......" sleep 5 continue else break fi done ps aux | grep 'mysql' | grep -v 'grep' | grep -v 'bash' if [ $? -eq 0 ] then echo "MySQL安装完毕。" else echo "MySQL安装失败。" fi ####################更改root账号随机密码 random_passwd=`grep 'temporary password' $DATA_DIR/log/error.log | awk -F 'root@localhost: ' '{print $2}'` /usr/local/mysql/bin/mysql -S /tmp/mysql_$dbname.sock -p"$random_passwd" --connect-expired-password -e "set sql_log_bin=0;alter user root@'localhost' identified by '$root_passwd';" echo "root账号随机密码更改完毕。" ####################创建同步账号 /usr/local/mysql/bin/mysql -S /tmp/mysql_$dbname.sock --connect-expired-password -p"$root_passwd" -e "set sql_log_bin=0;create user '$repl_user'@'%' IDENTIFIED BY '$repl_passwd'; GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO '$repl_user'@'%';" sed -i -r "s/(PATH=)/\1\/usr\/local\/mysql\/bin:/" /root/.bash_profile source /root/.bash_profile echo "MySQL账号初始化完毕。" ################################################
3.使用示例
Nothing is trivial at all；
浙公网安备 33010602011771号