MySQL数据库的主从同步
<h2>1.安装前准备</h2>
<pre><code class="language-bash">主 机：172.16.7.211 mysql-master
从 机：172.16.7.212 mysql-slave
OS : CentOS Linux release 7.9.2009 (Core)
MYSQL: Percona-Server-5.7.26-29-Linux.x86_64.ssl101.tar.gz</code></pre>
<h2>2. 下载MYSQL安装包</h2>
<pre><code class="language-bash">wget https://www.percona.com/downloads/Percona-Server-5.7/Percona-Server-5.7.26-29/binary/tarball/Percona-Server-5.7.26-29-Linux.x86_64.ssl101.tar.gz</code></pre>
<h2>3.解压</h2>
<pre><code class="language-bash">tar -zxvf Percona-Server-5.7.26-29-Linux.x86_64.ssl101.tar.gz -C /usr/local/
cd /usr/local/
mv Percona-Server-5.7.26-29-Linux.x86_64.ssl101/ mysql5726</code></pre>
<h2>4. 创建用户及相关目录并对目录赋权</h2>
<h3>4.1 创建组和用户</h3>
<pre><code class="language-bash">groupadd mysql
useradd mysql -g mysql -d /usr/local/mysql -s /sbin/nologin</code></pre>
<h3>4.2.创建目录(6006为mysql的端口)</h3>
<pre><code class="language-bash">mkdir -p /etc/mysql
mkdir -p /mysqldata/my6006/{data,socket,log,tmp}</code></pre>
<h3>4.3、相关目录赋权</h3>
<pre><code class="language-bash">chown -R mysql:mysql /etc/mysql
chown -R mysql:mysql /usr/local/mysql5726
chown -R mysql:mysql /mysqldata/my6006</code></pre>
<h2>5. 创建配置文件</h2>
<h3>5.1 创建配置文件my6006.cnf</h3>
<pre><code class="language-bash">vim /etc/mysql/my6006.cnf</code></pre>
<p>输入如下内容：</p>
<pre><code class="language-bash">[client]
port = 6006
socket = /mysqldata/my6006/socket/mysqld.sock
default-character-set = utf8mb4
# The MySQL server
#---Basic---#
[mysqld]
read-only = 0 # 主机写：0，从机只读：1；
server-id = 72116006 # 主机时写主机IP+PORT,IP为后两段值，如：172.16.7.211值为7211
report_host = 172.16.7.211 # 让master自动发现slave,方便mysqlfailover工具发现。主机时此参数无效，从机时写主机IP
report-port=6006
extra_port = 6106 #管理员专用通道
extra_max_connections=3 #管理员专用通道最大数量
port = 6006
user = mysql
basedir = /usr/local/mysql5726/
datadir = /mysqldata/my6006/data
tmpdir = /mysqldata/my6006/tmp
socket = /mysqldata/my6006/socket/mysqld.sock
log_bin = /mysqldata/my6006/log/mysql-bin.log
pid-file = /mysqldata/my6006/socket/mysql.pid
skip-external-locking
skip-name-resolve
lower_case_table_names = 1
default-time_zone = '+8:00'
default-storage-engine = INNODB
character-set-server = utf8mb4
connect_timeout = 10
wait_timeout = 28800
interactive_timeout = 28800
back_log = 1024
event_scheduler = OFF
open_files_limit = 65535
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
innodb_flush_neighbors=0 #sas盘建议开启. ssd pci-e 等存储设备建议关闭
thread_cache_size = 1024
query_cache_type = 0
query_cache_size = 0
#init-connect = 'insert into auditdb.accesslog (connectionid,conn rname,privmatchname,logintime) values(connection_id(),user(),current_user(),now());'
#log_warnings = 2
log_warnings = 1
#---binlog---#
log-bin = /mysqldata/my6006/log/mysql-bin
binlog_format = row
max_binlog_size = 1024M
binlog_cache_size = 24M #之前是64M ， 这是一次性分配的会话级别变量，正确情况下10M足够。
expire-logs-days = 7
sync_binlog = 1
#sync_relay_log=1
log-slave-updates=1 #级联复制需要开启
master_info_repository=table #之前是关闭的，开启有利于数据安全，主要针对slave掉电挂掉的情况
relay_log_info_repository=table #之前是关闭的，开启有利于数据安全,主要针对slave掉电挂掉的情况
#---replication---#
slave-net-timeout = 10
#半同步复制
#rpl_semi_sync_master_enabled = 1 #master开启半同步复制
#rpl_semi_sync_master_wait_no_slave = 1 #是否允许master 每个事物提交后都要等待slave的receipt信号。默认为on ，每一个事务都会等待，如果slave宕掉后，当slave追赶上master的日志时，可以自动的切换为半同步方式，如果为off,则slave追赶上后，也不会采用半同步的方式复制了，需要手工配置。
#rpl_semi_sync_master_timeout = 1000 #主库在某次事务中，如果等待时间超过1000毫秒，那么则降级为普通模式，不再等待备库
#rpl_semi_sync_slave_enabled = 1 #slave 开启半同步复制
skip-slave-start
#log_slave_updates = 1
relay_log_recovery = 1
relay_log = mysqld-relay-bin
#GTID
#gtid_mode=on
#enforce-gtid-consistency=on
#gtid_domain_id = 0 针对mariadb可用
#---slow log---#
slow_query_log = 1
slow_query_log_file = /mysqldata/my6006/log/mysql-slow.log
long_query_time = 0.5
#---error log---#
log-error = /mysqldata/my6006/log/error.log
#---per_thread_buffers---#
max_connections=8000
max_user_connections=5000
max_connect_errors=1000000
key_buffer_size = 64M
max_allowed_packet = 1024M
table_open_cache = 6144
table_definition_cache = 4096
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
join_buffer_size = 8M #之前是2M，本参数和BNL优化器有关系，适当调高
tmp_table_size = 256M
max_heap_table_size = 256M
query_cache_type=0
query_cache_size = 0
query_cache_strip_comments = 1
bulk_insert_buffer_size = 32M
thread_cache_size = 1024
#thread_concurrency = 32 主要针对solaris8之前的系统
thread_stack = 512K
#--- InnoDB ---#
innodb_data_home_dir = /mysqldata/my6006/data
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 1024M
innodb_buffer_pool_instances = 21 #通常1.3G一个pool,最大64.
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 20
innodb_sync_spin_loops = 40
innodb_max_dirty_pages_pct = 75
innodb_support_xa = 1
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 = 2000
innodb_purge_threads=4 #适当跳大此线程数,加速DML,硬件能够支持的住。
innodb_purge_batch_size = 300 #5.6.3以后默认就是300.
#innodb_old_blocks_pct=75
innodb_change_buffering=all #之前是insert，建议使用默认设置，应对更多场景。 上次的qc，update操作很多，修改这个效果很明显。具体值可以根据实际情况动态修改。
innodb_stats_on_metadata=0
optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on' # 新添加项，目的是利用5.6的MRR，ICP，BKA 优化器
#transaction_isolation = READ-COMMITTED
#skip-innodb_adaptive_hash_index此参数在聚合支付中还是注释掉，加快基于索引数据量比较大的在内存中开辟一块hash存入索引hash
#skip-innodb_adaptive_hash_index #5.6或者更高的版本，通常禁用hash-index 适应性更好， SHOW ENGINE INNODB STATUS command. If you see many threads waiting on an RW-latch created in btr0sea.c。 需要关闭hash——index,2019-03-01但是聚合支付要求启用hash-index,也就要把这个参数注释
#---new---#
eq_range_index_dive_limit =10000 #优化 where 条件中 in 语句
#innodb_log_block_size = 512 这是默认值
#numa
#thread pool
# thread_handling=pool-of-threads
# thread_pool_oversubscribe=15 #默认是3，在一主多从中有风险
# thread_pool_stall_limit = 50 #默认是500毫秒
#
#innodb_corrupt_table_action = warn
#innodb_buffer_pool_dump_at_shutdown = 1 #在shutdown时把热数据dump到本地磁盘
#innodb_buffer_pool_load_at_startup = 1 #在启动时把热数据加载到内存
#
#innodb_kill_idle_transaction = 5 #类似于pt-kill
#innodb_fake_changes = 1 #重启备库时对其进行预热，以加快复制的速度
auto_increment_increment=1
auto_increment_offset=1
[mysqldump]
quick
max_allowed_packet = 1024M
myisam_max_sort_file_size = 10G
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 256k
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 68192
flush_caches = 1
numa_interleave = 1</code></pre>
<p>需要修改的几个参数：
server-id = 3856006 #server-id自定义一般ip后两段+端口号
report_host = 192.168.3.85 #从机时修改成主机dbserver的host ip
innodb_buffer_pool_size = 1024M #如果是数据库专用则建议65%*物理内存大小
max_allowed_packet = 1024M #最大包大小，根据业务实际需求设置</p>
<h2>6. 初始化</h2>
<pre><code class="language-bash">/usr/local/mysql5726/bin/mysqld --defaults-file=/etc/mysql/my6006.cnf --user=mysql --initialize</code></pre>
<p>查找原始密码：</p>
<pre><code class="language-bash">cat /mysqldata/my6006/log/error.log |grep root</code></pre>
<h2>7. 启动数据库</h2>
<pre><code class="language-bash">/usr/local/mysql5726/bin/mysqld_safe --defaults-file=/etc/mysql/my6006.cnf --user=mysql &</code></pre>
<p>检查有无报错：/mysqldata/my6006/log/error.log
启动后查看进程，有两个进程在说明启动成功</p>
<pre><code class="language-bash">ps -ef |grep mysqld</code></pre>
<h2>8. 修改root用户密码</h2>
<p>登录前处理</p>
<h3>8.1 command not found</h3>
<pre><code class="language-bash">mysql -uroot -p
-bash: mysql: command not found</code></pre>
<p>解决办法：</p>
<pre><code class="language-bash">ln -s /usr/local/mysql5726/bin/mysql /usr/bin/</code></pre>
<h3>8.2 ERROR 2002 (HY000)</h3>
<pre><code class="language-bash">mysql -uroot -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)</code></pre>
<p>解决办法：</p>
<pre><code class="language-bash"># kill -9 PID 保证/mysqldata/my6006/socket/mysqld.sock文件生成,再做软链，否则重启无效。
# ll /mysqldata/my6006/socket/mysqld.sock
ln -s /mysqldata/my6006/socket/mysqld.sock /tmp/mysql.sock</code></pre>
<h3>8.3 找到临时root密码</h3>
<pre><code class="language-bash">cat /mysqldata/my6006/log/error.log |grep root</code></pre>
<h3>8.4 修改root用户密码</h3>
<pre><code class="language-bash">mysql> alter user 'root'@'localhost' identified by 'newpasswd';</code></pre>
<h3>8.5 配置随机启动</h3>
<pre><code class="language-bash">chmod +x /etc/rc.local
echo '/usr/local/mysql5726/bin/mysqld_safe --defaults-file=/etc/mysql/my6006.cnf --user=mysql &' >> /etc/rc.local</code></pre>
<h2>9. 九、配置主从</h2>
<h3>9.1 在主机上执行</h3>
<pre><code class="language-bash">mysql> create user 'rep_slave'@'172.16.7.212' identified by '310012';
grant replication slave,replication client on *.* to 'rep_slave'@'172.16.7.212';</code></pre>
<p>说明：IP为从机的IP</p>
<h3>9.2 在从机上执行</h3>
<pre><code class="language-bash">mysql> CHANGE MASTER TO MASTER_HOST='172.16.7.211',MASTER_USER='rep_slave',MASTER_PASSWORD='310012' ,MASTER_PORT=6006, MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=891;</code></pre>
<p>说明：
1.IP为主机的IP
2.MASTER_LOG_FILE='mysql-bin.000003' #值为：File=mysql-bin.000003中的值，在主上show master status命令找到
3.MASTER_LOG_POS=891 #值为：Position=891中的值，在主上show master status命令找到</p>
<h3>9.3 户口动slave服务</h3>
<pre><code class="language-bash">mysql> start slave;</code></pre>
<h3>9.4 主从服务状态</h3>
<p>主机上查看服务状态：</p>
<pre><code class="language-bash">mysql> show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 891 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)</code></pre>
<p>从机上查看服务状态：</p>
<pre><code class="language-bash">mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.7.211
Master_User: rep_slave
Master_Port: 6006
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 891
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 891
Relay_Log_Space: 528
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 72116006
Master_UUID: 4dbdd556-41b1-11eb-a615-005056bd20c7
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)</code></pre>
<p>有如下几个关键参数是否运行正常：</p>
<pre><code class="language-bash">Slave_IO_Running: Yes
Slave_SQL_Running: Yes</code></pre>
<h2>10. 物理备份</h2>
<h3>10.1 安装xtrabackup备份工具</h3>
<pre><code class="language-bash">cd /opt/
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/\
binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm
yum localinstall percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm</code></pre>
<h3>10.2 编写备份脚本，crontab定时调用</h3>
<pre><code class="language-bash">touch /data/mysql_ backup.sh
vim /data/mysql_backup.sh
bakdate=`date "+%Y%m%d_%H%M%S"`
deldate=`date -d '15 day ago' '+%Y%m%d'` #删除过期备份的天数
bakpath='/Data/mysqlbackup' #备份路径
config_file=/etc/mysql/my6006.cnf #mysql服务配置文件路径
user=root #数据训用户名
password=XXXXXXX #数据训用户密码
socket=/Data/my6006/socket/mysqld.sock #mysql服务socket文件路径
cd $bakpath
innobackupex --defaults-file=$config_file --user=$user --password=$password --socket=$socket --parallel=2 --no-version-check --no-timestamp --stream=tar $bakpath | gzip > mysqlbackup_$bakdate.tgz
rm -rf $bakpath/mysqlbackup_$deldate*.tgz
crontab -e
30 0 * * * sh /data/mysql_ backup.sh</code></pre>
<h2>11、配置允许root远程登录</h2>
<p><a href="https://www.cnblogs.com/goxcheer/p/8797377.html">https://www.cnblogs.com/goxcheer/p/8797377.html</a></p>
<p>MySql-Server 出于安全方面考虑默认只允许本机(localhost, 127.0.0.1)来连接访问.</p>
<pre><code class="language-bash">[root@localhost etc]# mysql -u root -p</code></pre>
<p>连接后，操作mysql数据库</p>
<pre><code class="language-bash">MariaDB [mysql]> use mysql；</code></pre>
<p>查询用户命令</p>
<pre><code class="language-bash">MariaDB [mysql]> select User,authentication_string,Host from user;
+-------------+-----------------------+-----------+
| User | authentication_string | Host |
+-------------+-----------------------+-----------+
| mariadb.sys | | localhost |
| root | invalid | localhost |
| mysql | invalid | localhost |
+-------------+-----------------------+-----------+</code></pre>
<p>这里也可以看出host默认都是localhost访问权限
接下来就是最重要的部分了：</p>
<pre><code class="language-bash">MariaDB [mysql]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456';</code></pre>
<p>这里的123456为你给新增权限用户设置的密码，%代表所有主机，也可以具体到你的主机ip地址</p>
<pre><code class="language-bash">MariaDB [mysql]> flush privileges;</code></pre>
<p>这一步一定要做，不然无法成功！ 这句表示从mysql数据库的grant表中重新加载权限数据
因为MySQL把权限都放在了cache中，所以在做完更改后需要重新加载。
执行完这两步，再次查询用户表命令：</p>
<pre><code class="language-bash">MariaDB [mysql]> select User,authentication_string,Host from user;
+-------------+-------------------------------------------+-----------+
| User | authentication_string | Host |
+-------------+-------------------------------------------+-----------+
| mariadb.sys | | localhost |
| root | invalid | localhost |
| mysql | invalid | localhost |
| root | *4F9D3C4DF16AA04558189C507A79CEA7DB63C2B5 | % |
+-------------+-------------------------------------------+-----------+</code></pre>
<p>发现多了一个用户，该用户所有的主机都可以访问，此时再次用sqlyog访问连接成功！</p>
<h2>12、修改字符集</h2>
<pre><code class="language-bash"></code></pre>