实验环境
两个CentOS7.X的虚拟机
MySQL-5.7.30
192.168.56.131 mysql-master 3306 主
192.168.56.132 mysql-slave 3307 从
关闭selinux
sed -i 's/SELINUX=enforcin八省联考成绩查询入口g/SELINUX=disabled/g' /etc/s巴沙鱼elinux/config
开启端口
firewall-cmd --add-port=3306/tcp --permanent 主
firewall-cmd --add-port=3307/tcp --permanent 从
firewall-cmd --reload #加载
firewall-cmd --list-all #查看开放端口
一、主从节点安装mysql
下载二进制部署包
wg苏卿陆容渊免费阅读et -O /usr/local/src/mysql创建表mysql-5.7.30-el7linux常用命令-x86_64.tar.gz https://downloads.mysql.com/archives/get/p/23/file/mysql-mysql怎么读5.7.30-el7-x86_64.tar.gz
安装配置
1、安装到/usr/lmysql密码忘记了怎么办ocal/mysql
[root@mysql-master src]# tar -xzf mysql-5.7.30-el7-x86_64.tar.gz -C /usr/local/ [root@mysql-master ~]# cd /usr/local [root@mysql-master local]# mv mysql-5.7.30-el7-x86_64/ mysql [root@mysql-master local]# cd mysql/ [root@mysql-master mysql]# ls bin docs include lib LICENSE man README share support-files
2、编辑配置文件/usr/local/mysql/my.cnf
vim my.cnf
[client]
port=3306
socket=/usr/local/mysql/run/mysql.sock
[mysql]
pid_file=/usr/local/mysql/run/mysqld.pid
[mysqld]
autocommit=1
general_log=off
explicit_defaults_for_timestamp=true
character-set-server=utf8
collation-server=utf8_general_ci
# system
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
max_allowed_packet=1g
max_connections=3000
max_user_connections=2800
open_files_limit=65535
pid_file=/usr/local/mysql/run/mysqld.pid
port=3306
server_id=1
skip_name_resolve=ON
socket=/usr/local/mysql/run/mysql.sock
tmpdir=/usr/local/mysql/tmp
sql_mode=NO_ENGINE_SUBSTITUTION
#binlog
log_bin=/usr/local/mysql/log/binlog/
binlog_cache_size=32768
binlog_format=row
expire_logs_days=7
log_slave_updates=ON
max_binlog_cache_size=2147483648
max_binlog_size=524288000
sync_binlog=100
#logging
log_error=/usr/local/mysql/log/error.log
slow_query_log_file=/usr/local/mysql/log/slow.log
log_queries_not_using_indexes=0
slow_query_log=1
log_slave_updates=ON
log_slow_admin_statements=1
long_query_time=2
#innodb
innodb_data_home_dir=/usr/local/mysql/log/iblog
innodb_log_group_home_dir=/usr/local/mysql/log/iblog
innodb_adaptive_flushing=ON
innodb_adaptive_hash_index=ON
innodb_autoinc_lock_mode=1
innodb_buffer_pool_instances=8
#default
innodb_change_buffering=inserts
innodb_checksums=ON
innodb_buffer_pool_size= 128M
innodb_data_file_path=ibdata1:32M;ibdata2:16M:autoextend
innodb_doublewrite=ON
innodb_file_format=Barracuda
innodb_file_per_table=ON
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_io_capacity=1000
innodb_lock_wait_timeout=10
innodb_log_buffer_size=67108864
innodb_log_file_size=1048576000
innodb_log_files_in_group=4
innodb_max_dirty_pages_pct=60
innodb_open_files=60000
innodb_purge_threads=1
innodb_read_io_threads=4
innodb_stats_on_metadata=OFF
innodb_support_xa=ON
innodb_use_native_aio=OFF
innodb_write_io_threads=10
[mysqld_safe]
datadir=/usr/local/mysql/data
3、创建目录
mkdir -p ./{data,log/{binlog,iblog},tmp,run}
4、创建用户和用户组
[root@mysql-master mysql]# groupadd mysql
[root@mysql-master mysql]# useradd -r -g mysql -s /sbin/nologin
5、将mysql目录属主和属组都修改为mysql
[root@mysql-master local]# chown -R mysql:mysql /usr/local/mysql/
6、初始化数据库
[root@mysql-master bin]# ./mysqld --defaults-file=/usr/local/mysql/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql
启动
[root@mysql-master bin]# ./mysqld_safe --defaults-file=/usr/local/mysql/my.cnf --skip-grant-tables --skip-networking & # 跳过密码验证登录
[root@mysql-master bin]# ./mysql -S /usr/local/mysql/run/mysql.sock # 登录
mysql> alter user 'root'@'localhost' identified by 'Daemon@123'; # 修改密码
mysql> exit
[root@mysql-master bin]# ./mysqld_safe --defaults-file=/usr/local/mysql/my.cnf --user=mysql & # 启动mysql
[root@mysql-master bin]# ./mysql -S /usr/local/mysql/run/mysql.sock -uroot -p'Daemon@123' # 登录
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
m宋庆玲ysql> exit
7、八省联考成绩查询入口关闭mysql命令
[root@mysql-master bin]# ./mysqladmin -uroot -p'Daemon@123' -S /usr/local/mysql/run/mysql.sock shutdown &
8、mysql创建表测试,用navicat连接数据库
这里的解决方案是修改 user 表中的Host为 %
mysql> select user,host from mysql.user; #查看
+---------------+-----------+
| user | host |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)
mysql> update mysql.user set host='%' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
mysql> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | % |
| mysql.session | localhost |
| mysql.sys | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)
二、配置主从
主从复制原则
1、每个slave只有一个master
2、每个slave只有一个服务id
3、一个ma神祇领主时代ster可以有多个slave
添加配置
1、修改master配置
[mysqld]
server-id=1
log-bin=mysql-bin
read-only=0 #可读可写
#需要同步的库
#binlog-do-db=water_data
#忽略的数据库
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
2、修改slave配置
[mysqld]
server-id=2
log-bin=mysql-relay
read-only=1 #可读可写
#需要同步的库
#binlog-do-db=water_data
#忽略的数据库
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
登录主库
1、在主库上创建用来连接的用户
mysql> create user 'tom'@'%' identified by 'tom123';
2、授权
mysql> grant replication slave on *.* to tom@'%' identified by 'tom123';
mysql> flush privileges; #刷新权限
3、查看授权信息
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000005 | 821 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
这里要记住"File"和"Position"的值,在从库上要用
登录从库
1、添加主库设置的用户信息
mysql> change master to master_host='192.168.56.131',master_port=3306,master_user='tom',master_password='tom123',master_log_file='binlog.000005',master_log_pos=821;
-> master_host='192.168.56.131',
-> master_port=3306,
-> master_user='tom',
-> master_password='tom123',
-> master_log_file='binlog.000005', #开始复制的二进制文件名(从主库查询结果中获取)
-> master_log_pos=821; #开始复制的二进制文件位置(从主库查询结果中获取)
Query OK, 0 rows affected, 2 warnings (0.01 sec)
2、启动从库,验证是否启动正确
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.131
Master_User: tom
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000010
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-slave-relay-bin.000011
Relay_Log_Pos: 317
Relay_Master_Log_File: binlog.000010
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: 154
Relay_Log_Space: 737
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: 8ef5f1fc-9d1d-11ec-9412-000c29d4c362
Master_Info_File: /usr/local/mysql/data/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)
如图,必须 Slave_IO_Running 和 Slave_SQL_Running 都为 Yes 才可以
如果为No解决办法
- IO为No
1、检查下主从的服务id,如果一致,需要修改为不同的id
2、检查主库用户的权限,
mysql> grant replication slave on . to tmysql创建表om@'%' identified by 'tom123';
mysql>mysql安装 flush privileges; #巴沙鱼为什么不能吃刷新权限
然后在从库上重新stoplinux常用命令 slave和start slave - SQL为No
mysql> stop slave;
mysql> set global sql_slave_skip_counter=1; #跳过错误步骤,后面步数可变
mysql> start slave;
mysql> show slave status\G
reset master;
reset slave all;
发表评论