linux下安装mysql和主从搭建_亲测成功

linux下安装mysql和主从搭建_亲测成功

linux下安装mysql

yum list installed | grep mysql  #查看系统中是否已安装mysql软件

yum -y remove mysql-libs.x86_64  #删除

mkdir -pv /data/software

cd /data/software/

wget http://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz

tar -zxvf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz

mv mysql-5.7.24-linux-glibc2.12-x86_64 mysql-5.7.24

mkdir -pv /data/datas/mysql/data  #创建数据保存目录
cat /etc/group |grep mysql  #查看mysql用户组
cat /etc/passwd |grep mysql #查看mysql用户

groupadd mysql #创建组
useradd mysql -g mysql #指定组创建用户
usermod -s /sbin/nologin mysql

mkdir -pv /data/apps
mv mysql-5.7.24 /data/apps/
chown -R mysql:mysql /data/apps/mysql-5.7.24
chown -R mysql:mysql /data/datas/mysql

cd /data/apps/mysql-5.7.24/bin/
./mysqld --user=mysql --basedir=/data/apps/mysql-5.7.24/ --datadir=/data/datas/mysql/data --initialize
#如果报libaio.so错：yum -y install libaio
#初始化mysql 成功之后记住密码  root@localhost: y%hrdMhc4wDf

cd /data/apps/mysql-5.7.24/support-files/
vim mysql.server
basedir=/data/apps/mysql-5.7.24
datadir=/data/datas/mysql/data

#将默认生成的my.cnf备份
mv /etc/my.cnf /etc/my.cnf.bak

./mysql.server start # 启动mysql成功
./mysql.server stop

配置mysql

mysql --version

ln -s /data/apps/mysql-5.7.24/bin/mysql /usr/bin/mysql

mysql --version

cp my-default.cnf /data/apps/mysql-5.7.24/my.cnf  #my-default.cnf新版本没有这个文件,网上找模版配置文件

cd /data/apps/mysql-5.7.24/

vim my.cnf  #修改配置文件
[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET  NAMES utf8mb4'

basedir = /data/apps/mysql-5.7.24
datadir = /data/datas/mysql/data

##设置开机启动
cp /data/apps/mysql-5.7.24/support-files/mysql.server /etc/init.d/mysqld

chmod 755 /etc/init.d/mysqld #可执行权限
chkconfig --list mysqld　 # 确认MySQL自启动
chkconfig mysqld on  #设置MySQL开启自启动
chkconfig --list mysqld
 mysqld  0:off 1:off 2:on 3:on 4:on 5:on 6:off　# 如果2--5为on的状态就OK

root密码与远程连接

service mysqld start

#初始化mysql用户root的密码
./bin/mysqladmin -uroot -p'y%hrdMhc4wDf' password 'root'  #y%hrdMhc4wDf为上面初始化mysql生成的随机密码

mysql -uroot -p  #输入密码进入

#mysql远程授权
mysql -uroot -p  #输入密码进入
grant all privileges on *.* to 'root'@'%' identified by 'root';
FLUSH PRIVILEGES;

#开放端口
vim /etc/sysconfig/iptables
-A INPUT -p tcp -m multiport --dports 3306 -j ACCEPT
service iptables restart

主从配置

主配置

cd /data/apps/mysql-5.7.24/

vim my.cnf
port = 3306
server_id = 52 #服务id，一般为ip后三位
#binlog-do-db = test #要同步的数据库
#binlog-ignore-db = mysql,sys,information_schema,performance_schema #不用同步的数据库,多个以逗号分隔
log-bin = mysql-bin #开启log-bin

max_binlog_size = 500M
binlog_cache_size = 2M
max_binlog_cache_size = 4M
expire_logs_days = 30
max_connections = 500
max_connect_errors = 10000
table_open_cache = 256
long_query_time = 1
slow-query-log
slow_query_log_file = /data/datas/mysql/data/slow_query_log_file.log


service mysqld restart #重启

mysql -uroot -p
create user 'repl'@'%' identified by '123456'; #创建用户
grant replication slave on *.* to 'repl'@'%' identified by '123456'; #授权
flush privileges;

show master status; #查看状态

从配置

cd /data/apps/mysql-5.7.24/

vim my.cnf
port = 3306
server_id = 53 #服务id，一般为ip后三位
read_only = 1 #只读

log-bin = mysql-bin
max_binlog_size = 500M
binlog_cache_size = 2M
max_binlog_cache_size = 4M
expire_logs_days = 30
max_connections = 500
max_connect_errors = 10000
table_open_cache = 256
long_query_time = 1
slow-query-log
slow_query_log_file = /data/datas/mysql/data/slow_query_log_file.log

relay_log = /data/datas/mysql/data/mysqld-relay-bin
  
relay_log-index = /data/datas/mysql/data/mysqld-relay-bin.index

service mysqld restart #重启

mysql -uroot -p
change master to master_host='主的ip', master_port=3306, master_user='repl', master_password='123456', master_log_file='mysql-bin.000002', master_log_pos=780;     
#mysql-bin.000002和780是从主里查的，show master status;命令

start slave; #启动从库复制线程
show slave status\G; #查看状态
#主要检查两个参数：Slave_IO_Running和Slave_Sql_Running。这两个值为Yes，OK从库配置好了

#接下来在 主数据库的操作都会同步到从数据库

-------注意---------注意-------注意-------------------
1.read_only=1只读模式，不会影响slave同步复制的功能，所以在MySQL slave库中设定了read_only=1后，通过 show slave status\G 命令查看salve状态，可以看到salve仍然会读取master上的日志，并且在slave库中应用日志，保证主从数据库同步一致；

2.read_only=1只读模式，可以限定普通用户进行数据修改的操作，但不会限定具有super权限的用户的数据修改操作；在MySQL中设置read_only=1后，普通的应用用户进行insert、update、delete等会产生数据变化的DML操作时，都会报出数据库处于只读模式不能发生数据变化的错误，但具有super权限的用户，例如在本地或远程通过root用户登录到数据库，还是可以进行数据变化的DML操作；

使用root连接从库时,可以做增加,修改,删除操作, 这时操作了从库数据,就会导致主从不能同步了. 所以项目中不应该用root去连接从库,最好是不要告诉root密码,创建一个普通用户给只读的权限

还有需要注意 设置同步的库不应包括mysql这个库去做同步,因为这个库里有用户信息也会同步过去,这样root用户密码什么的都会同步

从库

cd /data/apps/mysql-5.7.24/
./bin/mysqladmin -uroot -p'root' password 'et123456'  修改root本地登录密码
grant all privileges on *.* to 'root'@'%' identified by 'et123456'; 增加root远程访问和密码
grant select on *.* to 'xinyun'@'%' identified by 'xinyun'; 增加普通用户xinyun 远程只读权限访问和密码
FLUSH PRIVILEGES;

当从库数据不对时,把从库数据库删除,把主库导出来,然后导入从库里
主库root
show master status;
#show create database xinyun

从库root
stop slave; 停止同步进程

change master to master_host='192.168.1.52', master_port=3306, master_user='repl', master_password='123456', master_log_file='mysql-bin.000003', master_log_pos=457717; 重新设置同步的位置

start slave; 启动同步进程
show slave status; 查看状态

flush tables with read lock; 给所有的表加读锁的命令,super权限的用户也不能进行写修改操作
unlock tables; 解锁

set global read_only=1; 设置只读模式,限定普通用户进行数据修改的操作，但不会限定具有super权限的用户的数据修改操作
set global read_only=0; 解除只读模式

show global variables like "%read_only%";

---

主库完整配置

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It‘s a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET  NAMES utf8mb4'

basedir = /data/apps/mysql-5.7.24
datadir = /data/datas/mysql/data

port = 3306

server_id = 52
binlog-ignore-db = mysql,sys,information_schema,performance_schema
log-bin = mysql-bin

max_binlog_size = 500M
binlog_cache_size = 2M
max_binlog_cache_size = 4M
expire_logs_days = 30

max_connections = 500
max_connect_errors = 10000
table_open_cache = 256
long_query_time = 1
slow-query-log
slow_query_log_file = /data/datas/mysql/data/slow_query_log_file.log

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

从库完整配置

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It‘s a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET  NAMES utf8mb4'

basedir = /data/apps/mysql-5.7.24
datadir = /data/datas/mysql/data

port = 3306
server_id = 53
read_only = 1

log-bin = mysql-bin
max_binlog_size = 500M
binlog_cache_size = 2M
max_binlog_cache_size = 4M
expire_logs_days = 30

max_connections = 500
max_connect_errors = 10000
table_open_cache = 256
long_query_time = 1
slow-query-log
slow_query_log_file = /data/datas/mysql/data/slow_query_log_file.log

relay_log = /data/datas/mysql/data/mysqld-relay-bin
relay_log-index = /data/datas/mysql/data/mysqld-relay-bin.index

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES