#1024程序员节 | 征文
数据库安装（Linux版本 -mysql5.7）
目录
一、下载安装包
主数据库和备用数据库都按照以上步骤进行安装单节点：单节点安装步骤一到六
1.1下载安装包
wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.42-linux-glibc2.12-x86_64.tar.gz
1.2上传
将安装包上传到/usr/local/
二、基础参数设置
常用
-
vim [文件名] 创建与编辑文件
-
i 进入编辑状态 esc 退出
-
:wq 保存并退出
-
:q! 不保存推出
2.1创建并编辑sysctl.conf
/etc/sysctl.conf
执行命令
sudo vim /etc/sysctl.conf
输入配置
vm.swappiness = 0 net.ipv4.tcp_max_syn_backlog = 65535 net.ipv4.tcp_max_tw_buckets = 8000 net.ipv4.tcp_tw_reuse = 1 net.ipv4.tcp_tw_recycle = 1 net.ipv4.tcp_fin_timeout = 10 net.ipv4.ip_local_port_range = 1024 65535 net.ipv4.tcp_max_syn_backlog = 4096 net.ipv4.tcp_fin_timeout = 30 net.core.somaxconn = 65535 net.core.netdev_max_backlog = 65535 net.core.wmem_default = 87380 net.core.wmem_max = 16777216 net.core.rmem_default = 87380 net.core.rmem_max = 16777216
2.2创建并编辑limits.conf
/etc/security/limits.conf
执行命令
sudo vim /etc/security/limits.conf
输入配置
mysql soft nofile 65535 mysql hard nofile 65535 mysql soft nproc 20480 mysql hard nproc 65535
三、操作系统新增MySQL用户
3.1添加用户
sudo groupadd -g 505 mysql sudo useradd -u 505 mysql -d /home/mysql -g mysql -s /sbin/nologin
四、创建数据目录
-
重点：数据服务必须挂在独立的数据盘，且数据盘挂载目录为data
4.1创建数据目录并赋权限
sudo mkdir /data/mysql sudo chown -R mysql:mysql /data/mysql
五、安装数据库
5.1解压安装包并赋值权限
cd /usr/local sudo tar xzvf mysql-5.7.42-linux-glibc2.12-x86_64.tar.gz sudo mv /usr/local/mysql-5.7.42-linux-glibc2.12-x86_64 /usr/local/mysql sudo chown -R mysql. /usr/local/mysql
5.2编辑配置文件my.cnf
vim /etc/my.cnf
输入命令
sudo vim /etc/my.cnf
插入参数
注意:这里配置参数server_id 主库和从库请更换成不同的值。
[client] port = 3306 socket = /data/mysql/mysql.sock [mysqld] server_id=10 port = 3306 user = mysql character-set-server = utf8 log_bin = mysql-bin binlog_format = ROW expire_logs_days = 3 default_storage_engine = innodb log_timestamps = SYSTEM socket = /data/mysql/mysql.sock basedir = /usr/local/mysql datadir = /data/mysql pid-file = /data/mysql/mysqld.pid lower_case_table_names=1 max_connections = 5000 max_connect_errors = 1000 table_open_cache = 1024 max_allowed_packet = 128M open_files_limit = 65535 expire_logs_days=3 innodb_buffer_pool_size = 4096M innodb_file_per_table = 1 innodb_write_io_threads = 4 innodb_read_io_threads = 4 innodb_purge_threads = 2 innodb_flush_log_at_trx_commit = 1 innodb_log_file_size = 512M innodb_log_files_in_group = 2 innodb_log_buffer_size = 16M innodb_max_dirty_pages_pct = 80 innodb_lock_wait_timeout = 30 innodb_data_file_path=ibdata1:10M:autoextend log_error = /data/mysql/error.log slow_query_log = 1 long_query_time = 1 slow_query_log_file = /data/mysql/mysql-slow.log sql_mode=STRICT_TRANS_TABLES
5.3初始化MySQL数据库
/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/src/mysql-5.7.42 --datadir=/data/mysql
5.4添加MySQL路径到系统环境变量
sudo echo "export PATH=$PATH:/usr/local/mysql/bin" | sudo tee -a /etc/profile
5.5编辑环境变量（忽略步骤）
sudo nano /etc/profile 保存：ctrl+o 然后回车 退出：ctrl+x
5.6使新的环境变量立即生效
source /etc/profile
5.7复制MySQL服务脚本并启动MySQL服务
复制操作 sudo cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld 启动操作 sudo /etc/init.d/mysqld start
六、进入Mysql
6.1登录密码
mysql 密码存在/data/mysql/error.log 中的 root@localhost: XXXX，其中 xxx 指代具体的信息；
6.2ubuntu 20.4 安装 libncurses5
执行命令： sudo apt install apt-file sudo apt-file update sudo apt install libncurses5 sudo apt install libncuress*
6.3登录 mysql
mysql -uroot -p
6.4首次登录后无法使用用户操作，需要修改密码
ALTER USER USER() IDENTIFIED BY '密码';
七、配置主库（主数据库操作）
7.1创建用于主从复制的用户
CREATE USER 'repl'@'从库IP地址' IDENTIFIED WITH mysql_native_password BY '密码（自定义）';
7.2赋权
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'从库IP地址';
7.3刷新权限
FLUSH PRIVILEGES;
7.4查看主服务器状态
SHOW MASTER STATUS;
输出结果
+------------------+----------+--------------+------------------+-----------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-----------------------------+ | bin-log.000002 | 1471 | | | | +------------------+----------+--------------+------------------+-----------------------------+ 1 row in set (0.00 sec)
八、配置从库（备用数据库操作）
8.1连接主库
CHANGE MASTER TO MASTER_HOST='主库IP地址', MASTER_USER='repl', MASTER_PASSWORD='密码', --对应步骤7.1密码 MASTER_LOG_FILE='mysql-bin.000001', -- 对应7.4的File MASTER_LOG_POS=154; -- 对应7.4的Position
8.2开启主从复制
START SLAVE;
8.3查看从库状态
命令： SHOW SLAVE STATUS \G
输出： ↓↓↓↓↓↓↓↓ 请查看 “注意”
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.19.36
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 1471
Relay_Log_File: 192-relay-bin.000002
Relay_Log_Pos: 317
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes -（注意：这里必须为YES才代表链接成功）
Slave_SQL_Running: Yes -（注意：这里必须为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: 1471
Relay_Log_Space: 522
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: a35148ef-7cec-11eb-8e8e-000c29c5e6ab
Master_Info_File: /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: 9fd95dd6-7cec-11eb-8f02-000c29927484:1-3
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
九、测试主从
9.1主库执行
mysql> CREATE DATABASE test; Query OK, 1 row affected (0.00 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec)
9.2从库执行
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec)
扫一扫
384
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
