1
二进制安装MySQL5.7并搭建主从
source link: https://blog.51cto.com/forever8/5637810
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
二进制安装MySQL5.7并搭建主从
精选 原创二进制安装MySQL5.7并搭建主从
二进制安装MySQL5.7
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
官网可以下载最新版本
建立mysql用户
groupadd mysql
useradd -g mysql mysql -s /sbin/nologin
解压文件并赋权限
tar zxf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
# 移动到/usr/local/mysql
mv mysql-5.7.26-linux-glibc2.12-x86_64 /usr/local/mysql
# 切换到目录
cd /usr/local/mysql
chown mysql:mysql -R ../mysql
建立数据目录
mkdir -p /data/mysql/
chown -R mysql:mysql /data/mysql/
chmod 755 /data/mysql/
删除旧版本mysql/mariadb
rpm -qa|grep mysql
rpm -qa|grep mariadb
查看当前环境mysql/mariadb,如果有，则删除
编辑my.cnf配置文件
[mysqld]
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
basedir=/usr/local/mysql
datadir=/data/mysql
port=3306
pid-file = /data/mysql/mysql.pid
user = mysql
socket= /data/mysql/mysql.sock
bind-address = 0.0.0.0
server-id = 1
character-set-server = utf8
max_connections = 1000
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 128
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer_size = 4M
thread_cache_size = 8
query_cache_type = 1
query_cache_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
log_bin = mysql-bin
binlog_format = mixed
expire_logs_days = 30
log_error = /data/mysql/mysql-error.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql-slow.log
performance_schema = 0
explicit_defaults_for_timestamp
#lower_case_table_names = 1
skip-external-locking
default_storage_engine = InnoDB
#default-storage-engine = MyISAM
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 64M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
interactive_timeout = 28800
wait_timeout = 28800
#lower_case_table_names = 1
skip-external-locking
default_storage_engine = InnoDB
#default-storage-engine = MyISAM
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 64M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
interactive_timeout = 28800
wait_timeout = 28800
[client]
port=3306
socket = /data/mysql/mysql.sock
[mysql]
socket = /data/mysql/mysql.sock
启动数据库
./mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql --user=mysql --initialize
./mysqld_safe --defaults-file=/etc/my.cnf &
登录数据库
# 查看数据库临时（方案1，如果mysql不能登录，用方案2更改）
cat /data/mysql/error.log | grep password
./mysql -u root -p
SET PASSWORD = 'root123'; #更改密码
ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER; #永不过期
flush privileges; #刷新权限
#方案2 ：mysqld 启动时不对密码进行验证
vim /etc/my.cnf
在 [mysqld] 小节下添加一行：skip-grant-tables=1 。这一行配置让 mysqld 启动时不对密码进行验证
MySQL [(none)]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MySQL [mysql]> update user set authentication_string = password('*********'), password_expired = 'N', password_last_changed = now() where user = 'root';
搭建主从复制
- MySQL之间数据复制的基础是二进制日志文件（binary log file）
- 一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中
- 其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化
- 如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中
- 然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制
准备两台服务器，一主一从
192.168.56.104 主
192.168.56.105 从
- 找到mysql配置文件my.cnf或my.ini(各个系统的存放位置不同,可以针对操作系统进行百度)
进入配置文件在[mysqld]部分插入或修改
[mysqld]
log_bin=mysql-bin #开启二进制日志
server-id=100 #设置server-id,设置为当前ip的最后一个段的数字,这样不会乱
- 重启mysql
- 分别为从库创建对应的用户
首先连接mysql 使用 (mysql -u 用户名 -p 密码) 进行连接 远程可以使用 (mysql -h 服务器ip -u 用户名 -p 密码)
CREATE USER 'slave'@'192.168.56.105' IDENTIFIED BY '*******';#创建用户
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.56.105';#分配权限
flush privileges; #刷新权限
- 查看主库状态,日志文件名,日志位置(记录文件名与位置)
MySQL [(none)]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 | 777 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
1.同样找到msyql配置文件修改server-id，两个从库分别修改为
[mysqld]
server-id=104 #设置server-id，192.168.56.104
- 重启从库mysql服务
3.打开mysql回话执行同步sql
CHANGE MASTER TO MASTER_HOST='192.168.56.104', MASTER_USER='slave', MASTER_PASSWORD='*********', MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=777;
start slave;
show slave status\G查看主从是否搭建成功
- 打赏
- 赞
- 收藏
- 评论
- 分享
- 举报
上一篇：DNS介绍
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK