All the paths in this article are followed by an article: CentOS 7 multi-instance of yum installation MariaDB configuration https://blog.csdn.net/u011342720/article/details/83213558
Description: The following port is 3307 instance as main database and 3308 corresponding instance as slave database
Master database configuration file
cd /home/multiMysql/
vim ./etc/3307.cnf
Add:
server-id=3307
log-bin=master-bin
log-bin-index=master-bin.index
binlog-do-db=test1
[client] port = 3307 socket = /home/multiMysql/socket/mysql3307.sock [mysqld] datadir=/home/multiMysql/datadir/3307 port = 3307 socket = /home/multiMysql/socket/mysql3307.sock tmpdir=/home/multiMysql/tmp/3307 myisam-recover-options = BACKUP server-id=3307 log-bin=master-bin log-bin-index=master-bin.index binlog-do-db=test1
Restart the main database:
[root@server187 multiMysql]# ./bin/mysql3307 restart Stoping MySQL... Starting MySQL... Start MySql OK! PORT:3307 [root@server187 multiMysql]#
View the status of the main library:
[root@server187 multiMysql]# mysql -u root -p -S ./socket/mysql3307.sock Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 15 Server version: 10.3.8-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show master status; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-bin.000002 | 771 | test1 | | +-------------------+----------+--------------+------------------+ 1 row in set (0.000 sec)
Keep in mind: File, Position, etc.
Configuration file from database
vim ./etc/3308.cnf
Add:
server-id=3308
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
replicate_do_db=test1
[client] port = 3308 socket = /home/multiMysql/socket/mysql3308.sock [mysqld] datadir=/home/multiMysql/datadir/3308 port = 3308 socket = /home/multiMysql/socket/mysql3308.sock tmpdir=/home/multiMysql/tmp/3308 myisam-recover-options = BACKUP server-id=3308 relay-log-index=slave-relay-bin.index relay-log=slave-relay-bin replicate_do_db=test1
Restart the slave library:
[root@server187 multiMysql]# ./bin/mysql3308 restart Stoping MySQL... Starting MySQL... Start MySql OK! PORT:3308 [root@server187 multiMysql]#
Back to the main library: and into the database
mysql -u root -p -S ./socket/mysql3307.sock
Set slave library permissions in the main library:
GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.2.187:3308' IDENTIFIED BY 'XXXX'; CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000002',MASTER_LOG_POS=771;
The main library configuration is basically complete.
Continue from the library: Enter the slave Library
[root@server187 multiMysql]# mysql -u root -p -S ./socket/mysql3308.sock Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 25 Server version: 10.3.8-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
Setting the path of the main library: Note that some versions are written differently, here is the change master
CHANGE MASTER TO MASTER_HOST='192.168.2.187',MASTER_USER='root',MASTER_PASSWORD='XXX',MASTER_PORT=3307,MASTER_CONNECT_RETRY=60,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=107
Start: (start slave), some versions are written as: slave start, according to their own circumstances to determine.
start slave
Test results:
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: root
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 343
Relay_Log_File: slave-relay-bin.000004
Relay_Log_Pos: 643
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test1
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: 343
Relay_Log_Space: 1252
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: 3307
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 1
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 1
1 row in set (0.001 sec)
ERROR: No query specified
Among them: Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test1
The first two = yes instructions are OK, then add tables, write data, modify data in the main database, you can see the corresponding data from the library.
In addition, the problems encountered are explained.
1. Slave_IO_Running: NO, many of which are log files with different starting positions in the database, leading to NO
Question presentation: Last_IO_Error: Got fatal error 1236 from master when reading data from binary log:'Can not find the first log file name in binary log index file'
Or:
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'bogus data in log event; the first event 'master-bin.000001' at 107, the last event read from 'master-bin.000001' 126.'
Solution:
stop slave;
The master_log_file below, master_log_pos, is the location of the file to be remembered in front of you. It must not be wrong.
MariaDB [test1]> CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000001',MASTER_LOG_POS=329; Query OK, 0 rows affected (0.072 sec)
MariaDB [test1]> start slave; Query OK, 0 rows affected (0.001 sec)
Then: show slave status\G;
2. This time we only deal with the specific database test1, other settings are not verified, you can try:
Primary other database configuration:
binlog-ignore-db=mysql // / database to be ignored
Configuration from several other databases:
replicate-ignore-db=mysql // / database to be ignored
readonly
slave-skip-errors=all
slave-net-timeout=60