msyqllogo.jpg
1. 主数据库配置： /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/home/software/data/mysql
socket=/tmp/mysql.sock
user=mysql
server_id=1 #服务器id (主从必须不一样)
port=3306
log-bin=mysql-bin #打开日志(主机需要打开)，这个mysql-bin也可以自定义，这里也可以加上路径
#binlog-do-db= #要给从机同步的库
binlog-ignore-db=mysql #不给从机同步的库(多个写多行)
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
expire_logs_days=7 #自动清理 7 天前的log文件,可根据需要修改
2. 重启主服务器并查看MASTER相关数据：
service mysqld restart #重启数据库
mysql> show variables like '%log_bin%';
+---------------------------------+-------------------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------------------+
| log_bin | ON |
| log_bin_basename | /home/software/data/mysql/mysql-bin |
| log_bin_index | /home/software/data/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------------------------------------------+
6 rows in set (0.02 sec)
3. 创建从库连接主库同步数据账户：
mysql> grant replication slave on *.* to 'copyusr'@'%' identified by '123456';
验证用户数据
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user | authentication_string | host |
+---------------+-------------------------------------------+-----------+
| root | *D93DB9B2EB76CFA26400B9F902FB7F06DE5CE686 | % |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| copyusr | *C4FAA3B6872CA0D7DE0F19008194BC3E718E3236 | % |
+---------------+-------------------------------------------+-----------+
4 rows in set (0.00 sec)
4. 查看主数据库状态数据：
mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000001 | 1208 | | mysql,information_schema,performance_schema,sys | |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)
5. 从数据库配置： /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/home/software/data/mysql
socket=/tmp/mysql.sock
user=mysql
server_id=2
port=3306
read_only=1
6. 重启从数据库并与主数据库建立连接：
重启服务器
service mysqld restart
建立主数据库连接：
mysql> stop slave;
mysql> change master to
-> master_host='192.168.1.222', # master的ip
-> master_user='copyusr', # 备份用户账户
-> master_password='123456', # 备份用户密码
-> master_log_file='mysql-bin.000001', #上面截图，且要与master的参数一致
-> master_log_pos=1208; #上面截图，且要与master的参数一致
mysql> start slave;
查看从数据库状态：
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.3.66
Master_User: copyusr
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1208
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 1095
Relay_Master_Log_File: mysql-bin.000001
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: 1208
Relay_Log_Space: 1306
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: 1
Master_UUID: 0470473a-9f57-11eb-a03c-000c2930d0fc
Master_Info_File: /home/software/data/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:
1 row in set (0.00 sec)
目前MySQL主从配置已经完成，
可以通过查看上面从库数据中的
Slave_IO_Running: Yes # 与主数据网络连接情况
Slave_SQL_Running: Yes # 同步数据SQL执行情况
可以在主数据建库，建表，写入数据，正常运行，从库数据应该可以同步成功~
网友评论