mysql 主从部署
1.准备3台服务器 （如果是镜像的同一个mysql 需要注意，同步的时候要修改uuid）
192.168.0.74 主服务器
192.168.0.75 从服务器
192.168.0.18 maxscale 服务器
2.配置主服务器 192.168.0.74
1.vim /etc/my.cnf
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid #指定服务器id server_id=74 #启用binlog日志，并指定文件名后缀 log-bin=master74 ## 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步） binlog-ignore-db=mysql binlog-ignore-db=sys binlog-ignore-db=information_schema binlog-ignore-db=performance_schem ## 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存 binlog_cache_size=16M ### 主从复制的格式（mixed,statement,row，默认格式是statement） binlog_format=mixed ### 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。 expire_logs_days=30 ### 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。 ### 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致 slave_skip_errors=1062 ### 控制binlog的写入频率。每执行多少次事务写入一次 ### 这个参数性能消耗很大，但可减小MySQL崩溃造成的损失，为0表示不控制 sync_binlog = 1 innodb_flush_log_at_trx_commit = 1 ~
2.重启生效 systemctl restart mysqld
2.主服务器添加授权用户，并查看bInlog日志信息
登录主服务器
mysql -u root -p 123456
mysql> grant all on *.*to repluser@'%' identified by '111111';
mysql> show master status;
3.从服务器配置
vim /etc/my.cnf
[mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid server_id=75 ## 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步） binlog-ignore-db=mysql binlog-ignore-db=sys binlog-ignore-db=information_schema binlog-ignore-db=performance_schem ## 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存 binlog_cache_size=16M ### 主从复制的格式（mixed,statement,row，默认格式是statement） binlog_format=mixed ### 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。 expire_logs_days=30 ### 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。 ### 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致 slave_skip_errors=1062 ### relay_log配置中继日志 relay_log=mysql-relay-bin ### log_slave_updates表示slave将复制事件写进自己的二进制日志 ### 主要为了作为其他的master log_slave_updates=ON ### 防止改变数据(除了特殊的线程) read_only=1 #(为了使备机随时转正，所以这里允许写) ### MySQL主从复制的时候，当Master和Slave之间的网络中断，但是Master和Slave无法察觉的情况下（比如防火墙或者路由问题）。Slave会等待slave_net_timeout设置的秒数后，才能认为网络出现故障， 然后才会重连并且追赶这段时间主库的数据,默认60 slave-net-timeout = 20 ### 如果启用，此变量将在服务器启动后立即启用自动中继日志恢复。 relay_log_recovery = ON ### 该变量确定从站在中继日志中的位置是写入FILE还是写入表 relay_log_info_repository = TABLE
保存 生效 systemctl restart mysqld
3.登录 从服务器 配置好ip ，账号，密码 注意， master_log_file 的路径，master重启一次，这个值就会变化一次，要重新运行
change master to master_host='192.168.1.50',master_user='repluser',master_password='123456',master_log_file='master50.000001',master_log_pos=154;
show slave status\G; 查看同步状况
start slave; 开启同步
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master74.000002 Read_Master_Log_Pos: 629 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 794 Relay_Master_Log_File: master74.000002 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: 629 Relay_Log_Space: 1001 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: 74 Master_UUID: d1dcb1cd-5e38-11ec-9d29-fa202017ac1f Master_Info_File: /var/lib/mysql/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:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
表示成功
如果有false 则表示同步失败
报错： Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
表示从库和主库的uuid 相同
解决： 找到auto.cnf文件，修改里面的uuid值，保证各个db的uuid不一样，重启db即可
[root@localhost ~]# find / -name auto.cnf /var/lib/mysql/auto.cnf [root@localhost ~]# cat /var/lib/mysql/auto.cnf //查看主数据库auto.cnf文件中的UUID信息 [auto] server-uuid=e46c9961-5780-11ea-bf2f-000c291a8b6b [root@localhost ~]# mysql -uroot -p123qqq...A ... mysql> show variables like '%server_uuid%'; //查看主数据库UUID +---------------+--------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------+ | server_uuid | e46c9961-5780-11ea-bf2f-000c291a8b6b | +---------------+--------------------------------------+
更改从库数据
[root@dbsrv2 ~]# mv /data/mysqldata/auto.cnf /data/mysqldata/auto.cnf.bk ###重命名该文件
[root@dbsrv2 ~]# systemctl restart mysqld ###重启mysql
添加数据在主库上添加后，可以在从库上看到数据 从库上添加数据后，主库看不到
三、配置MaxScale代理服务器
maxscale下载地址：https://downloads.mariadb.com/MaxScale/
[root@instance-0k9n9mw6 solf]# rpm -ivh maxscale-2.4.5-1.centos.7.x86_64.rpm
warning: maxscale-2.4.5-1.centos.7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 28c12247: NOKEY
error: Failed dependencies:
libgnutls.so.28()(64bit) is needed by maxscale-2.4.5-1.x86_64
libgnutls.so.28(GNUTLS_1_4)(64bit) is needed by maxscale-2.4.5-1.x86_64
libgnutls.so.28(GNUTLS_3_0_0)(64bit) is needed by maxscale-2.4.5-1.x86_64
libgnutls.so.28(GNUTLS_3_1_0)(64bit) is needed by maxscale-2.4.5-1.x86_64
[root@instance-0k9n9mw6 solf]# rpm -ivh maxscale-2.4.5-1.centos.7.x86_64.rpm --force --nodeps
warning: maxscale-2.4.5-1.centos.7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 28c12247: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:maxscale-2.4.5-1 ################################# [100%]
修改 配置文件
vim /etc/maxscale.cnf
内容
2.4中移除MySQL驱动，全部统一使用MariaDB驱动，即配置文件中，选择驱动的时候，不需要修改**[MariaDB Monitor]为[MySQL Monitor]**
# MaxScale documentation: # https://mariadb.com/kb/en/mariadb-maxscale-24/ # Global parameters # # Complete list of configuration options: # https://mariadb.com/kb/en/mariadb-maxscale-24-mariadb-maxscale-configuration-guide/ [maxscale]
# 开启线程个数，默认为1.设置为auto会同cpu核数相同threads=auto# timestamp精度#ms_timestamp=1# 将日志写入到syslog中#syslog=1# 将日志写入到maxscale的日志文件中#maxlog=1# 不将日志写入到共享缓存中，开启debug模式时可打开加快速度#log_to_shm=0# 记录告警信息#log_warning=1# 记录notice#log_notice=1# 记录info#log_info=1# 不打开debug模式#log_debug=0# 日志递增#log_augmentation=1# 相关目录设置#basedir=/usr/local/maxscale/#logdir=/u01/maxscale/logs/trace/#datadir=/u01/maxscale/data/#cachedir=/u01/maxscale/cache/#piddir=/u01/maxscale/tmp/
# Server definitions # # Set the address of the server to the network # address of a MariaDB server. # [server1] type=server address=192.168.0.74 port=3306 protocol=MariaDBBackend #serv_weight=3 #读的比重 [server2] type=server address=192.168.0.75 port=3306 protocol=MariaDBBackend #serv_weight=3 #读的比重 # Monitor for the servers # # This will keep MaxScale aware of the state of the servers. # MariaDB Monitor documentation: # https://mariadb.com/kb/en/mariadb-maxscale-24-mariadb-monitor/ [MariaDB-Monitor] type=monitor module=mariadbmon servers=server1,server2 user=maxscale_monitor password=12345
# 心跳间隔20s
monitor_interval=2000# 如果有5.1的mysql版本,一定要设置此项,否者slave频繁报lost_slave#mysql51_replication=true
# 当slave断掉时,是否将所有访问指向master #detect_stale_master = true
# Service definitions # # Service Definition for a read-only service and # a read/write splitting service. # # ReadConnRoute documentation: # https://mariadb.com/kb/en/mariadb-maxscale-24-readconnroute/ #[Read-Only-Service] #type=service #router=readconnroute #servers=server1 #user=myuser #password=mypwd #router_options=slave # ReadWriteSplit documentation: # https://mariadb.com/kb/en/mariadb-maxscale-24-readwritesplit/ [Read-Write-Service] type=service router=readwritesplit servers=server1,server2 user=maxscale_route password=12345#所有的slave提供select查询服务
max_slave_connections=100%#master_accept_reads=true#master是否接受读请求
#auth_all_servers=true ##log_auth_warnings=true #身份验证失败和警告的日志记录，记录那些试图连接到MaxScale和来自哪里#filters=Hint #强制select走master的选项#允许slave落后master多少秒max_slave_replication_lag=3600
保存cnf
然后在主从服务器上配置 cnf中的账户
1.监视账户 这个地方感觉应该用到% 如果用到固定ip的话，客户端连接会失败
grant all privileges on *.* to 'maxscale_monitor'@'%' identified by '123456';
2.路由账户
grant all privileges on *.* to 'maxscale_route'@'%' identified by '123456';
然后运行 maxscale.cnf 如果报错 libgnutls.so.28, 安装 yum install gnutls
[root@instance-0k9n9mw6 solf]# maxscale -f /etc/maxscale.cnf maxscale: error while loading shared libraries: libgnutls.so.28: cannot open shared object file: No such file or directory [root@instance-0k9n9mw6 solf]# yum install gnutls
重新运行 maxscale.cnf 加用户运行
maxscale -f /etc/maxscale.cnf -U maxscale
查看是否运行成功
netstat -ntlup |grep maxscale
网上说的有配置
maxadmin -uadmin -pmariadb -P4016 这种的是低版本的，高版本的已经去掉这个了
直接运行 查看服务
maxctrl list services
maxctrl list servers
[root@instance-0k9n9mw6 solf]# maxscale -f /etc/maxscale.cnf Error: MaxScale cannot be run as root. Failed to write child process message! [root@instance-0k9n9mw6 solf]# maxscale -f /etc/maxscale.cnf -U maxscale
找到一台安装mysql的服务器 然后链接maxscale
mysql -u maxscale_monitor -p -h 192.168.0.18 -P4006 192.168.0.18是maxscale 服务器，4006是cnf中配置的监听ip maxscale_monitor 是在主机上创建的账号
然后 在maxscale 运行
maxctrl list services
maxctrl list servers
查看连接数
[root@instance-0k9n9mw6 solf]# maxctrl list servers ┌─────────┬──────────────┬──────┬─────────────┬─────────────────┬──────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼──────────────┼──────┼─────────────┼─────────────────┼──────┤ │ server1 │ 192.168.0.74 │ 3306 │ 1 │ Master, Running │ │ ├─────────┼──────────────┼──────┼─────────────┼─────────────────┼──────┤ │ server2 │ 192.168.0.75 │ 3306 │ 1 │ Slave, Running │ │ └─────────┴──────────────┴──────┴─────────────┴─────────────────┴──────┘ [root@instance-0k9n9mw6 solf]# maxctrl list services ┌────────────────────┬────────────────┬─────────────┬───────────────────┬──────────────────┐ │ Service │ Router │ Connections │ Total Connections │ Servers │ ├────────────────────┼────────────────┼─────────────┼───────────────────┼──────────────────┤ │ Read-Write-Service │ readwritesplit │ 1 │ 1 │ server1, server2 │ └────────────────────┴────────────────┴─────────────┴───────────────────┴──────────────────┘
