MySQL5.7數據庫主從架構部署,你再也不用去問度娘了
啰嗦的話,不多說,我們直接開始部署,我相信有點linux基礎的人都能看得懂。
Mysql5.7.22主從部署,既然是主從那肯定是需要至少2臺服務器了,2臺的安裝都是一樣的,請看下面!
1、 創建mysql用戶:useradd -m -d /home/mysql mysql
2、 上傳mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz安裝包到/app目錄下。
附官方下載鏈接:https://dev.mysql.com/downloads/file/?id=476936
3、 解壓安裝包到/app目錄下tar -zxvf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
4、 將解壓完的安裝包重命名為:mysql5722
- mv mysql-5.7.22-linux-glibc2.12-x86_64 mysql5722
5、 進入mysql5722目錄下創建data、logs目錄。
- cd mysql5722/
- mkdir data && mkdir logs
6、 進入剛剛創建的logs目錄下創建日志文件
- cd logs touch error.log && touch general.log
- 授權/app/mysql5722目錄屬主屬組為mysql
- chown -R mysql:mysql /app/mysql5722
7、 進入mysql的bin目錄下進行初始化設置:
- cd ../bin/ ./mysqld --initialize --user=mysql --basedir=/app/mysql5722/ --datadir=/app/mysql5722/data
注意:初始化的時候會提示root用戶的默認密碼記得保存,首次登陸需要。
8、 創建mysql配置文件:
- cd /etc/&& mv my.cnf my.cnf.bak
新建配置文件:touch my.cnf,內容如下:
Master節點配置文件內容:
- [mysqld]
- server-id=1
- log_bin=master-bin
- log_bin_index=master-bin.index
- binlog_do_db=mysql
- expire_logs_days=7
- #skip-grant-tables
- lower_case_table_names=1
- explicit_defaults_for_timestamp
- port = 3306
- datadir = /app/mysql5722/data
- socket = /app/mysql5722/mysql.sock
- user = mysql
- symbolic-links = 0
- basedir = /app/mysql5722
- big-tables
- character-set-server = utf8
- #general-log-file = /app/mysql5722/logs/general.log
- language = /app/mysql5722/share
- lc-messages-dir = /app/mysql5722/share
- general-log = on
- #GENERAL_LOG=ON
- general-log-file = /app/mysql5722/logs/mysqld.log
- slow-query-log = 1
- pid-file = /app/mysql5722/mysql.pid
- slow-query-log-file = /app/mysql5722/logs/mysqld_slow.log
- slave-load-tmpdir = /app/mysql5722/tmp
- plugin-dir = /app/mysql5722/lib/plugin
- max_connections = 1000
- #innodb
- #innodb_log_file_size=512M
- #innodb_buffer_pool_size=2G
- [mysqld_safe]
- log-error=/app/mysql5722/logs/mysql_error.log
- pid-file=/app/mysql5722/mysql.pid
- [client]
- port = 3306
- socket=/app/mysql5722/mysql.sock
- user=mysql
9、 配置啟動命令:
- cd /app/mysql5722/support-files/
- cp mysql.server /etc/init.d/mysqld
- cd /etc/init.d/
修改mysqld配置文件內容添加如下兩行信息:

10、 配置開機啟動
- chkconfig --add mysqld
- chkconfig mysqld on
- chown -R mysql:mysql mysql5722/
11、 啟動mysql數據庫
- service mysqld start
12、 登錄mysql
- ./mysql -uroot -p'LRabR;xaS9lh'
修改root默認密碼,不然無法執行sql:
- ./mysql -uroot -p'LRabR;xaS9lh'
- 修改root默認密碼,不然無法執行sql:
- alter user user() identified by "Abcd@123";修改root用戶密碼
- flush privileges;刷新
- user mysql;
- create user 'master'@'%' identified by 'Abcd@123';
- grant replication slave on *.* to 'master'@'%';
- flush privileges
- show master status;
備slave節點配置文件:
- [mysqld]
- server-id=2
- relay_log=slave-relay-bin
- relay_log_index=slave-relay-bin.index
- basedir=/app/mysql5722
- datadir=/app/mysql5722/data
- socket=/app/mysql5722/mysql.sock
- user=mysql
- character-set-server=utf8
- lower_case_table_names=1
- explicit_defaults_for_timestamp
- port=3306
- general-log=1
- general-log-file=/app/mysql5722/logs/general.log
- slow-query-log=1
- long_query_time=1
- slow_query_log_file=/app/mysql5722/slowquery.log
- pid-file=/app/mysql5722/mysql.pid
- slave-load-tmpdir=/app/mysql5722/slavetmp
- max_connections=1000
- # Disabling symbolic-links is recommended to prevent assorted security risks
- symbolic-links=0
- #skip-grant-tables
- [mysqld_safe]
- log-error=/app/mysql5722/logs/error.log
- pid-file=/app/mysql5722/mysql.pid
- [client]
- port=3306
- socket=/app/mysql5722/mysql.sock
- user=mysql
保存后重啟從節點mysql。
備機:(slave)上配置:
登錄mysql后執行:
- user mysql;
- change master to master_host='10.1.60.71',master_user='master',master_password='abcd@123',master_log_file='master-bin.000001',master_log_pos=154;
- show slave status\G;

保證如下兩個值都為YES說明主從復制已經配置成功,需要注意的是兩臺服務器的防火墻策略,網絡必須是互通的。
- Slave_IO_Running:YES
- Slave_SQL_Running:YES
當然此架構如果對外提供服務不想使用服務器的真實IP,你也可以安裝keepalived做VIP對外進行提供服務!