MYSQL搭建主从
Mysql搭建主从步骤，以前的笔记，不正确的话请指正。
一。数据主从搭建效果图
image.png
2.mysql节点的配置，master和slave的配置
2.1 Master 的配置
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
server_id=1
log-bin=binlog
log-bin-index=binlog.index
old_passwords=1
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
!includedir /etc/my.cnf.d
重启master
2.2 Slave 配置
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
server_id=2
log-bin=binlog
log-bin-index=binlog.index
old_passwords=1
relay_log_purge=0
relay_log = /var/lib/mysql/mysql-relay-bin
relay_log_index = /var/lib/mysql/mysql-relay-bin.index
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
!includedir /etc/my.cnf.d
- 操作master 配置权限
[botter@centos-oracle ~]$ sudo mysql -uroot -pmaxwit paipai
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.50-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [paipai]> GRANT replication slave ON . TO 'admin'@'%' identified by 'maxwit';
Query OK, 0 rows affected (0.00 sec)
MariaDB [paipai]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [paipai]> show master status ;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000001 | 475 | | |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)
4.slave 配置启动
出现的问题：master 允许3306端口
[slave@localhost ~]$ mysql -uroot -pmaxwit paipai
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.50-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [paipai]> change master to master_host='192.168.1.31',master_port=3306,master_user='admin',master_password='maxwit',master_log_file='binlog.000001',master_log_pos=475;
Query OK, 0 rows affected (0.01 sec)
MariaDB [paipai]> start slave;
Query OK, 0 rows affected (0.01 sec)
MariaDB [paipai]> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.1.31
Master_User: admin
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 475
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Connecting
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: 475
Relay_Log_Space: 245
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master 'admin@192.168.1.31:3306' - retry-time: 60 retries: 86400 message: Can't connect to MySQL server on '192.168.1.31' (113)
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)
ERROR: No query specified
