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密码,创建一个普通用户给只读的权限文章来源：https://www.toymoban.com/news/detail-856828.html
还有需要注意 设置同步的库不应包括mysql这个库去做同步,因为这个库里有用户信息也会同步过去,这样root用户密码什么的都会同步文章来源地址https://www.toymoban.com/news/detail-856828.html
从库
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
到了这里，关于linux下安装mysql和主从搭建_亲测成功的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
