编程知识 cdmana.com

Linux下Mysql标准化安装手册

服务器要求:

主机的内存,存储需满足数据库要求,对文件系统的要求如下:
/tran/mysql/	mysql软件安装目录,10G
/tran/my$port/	数据存放目录(port为端口号),根据数据的大小评估容量,其目录结构为:
根目录放置配置文件my.cnf,socket文件。
data目录作为my.cnf中的datadir目录,pid-file也放在这里。
log目录放置binlog,error log,slow log,relay log(slave)。

/tran/app/	DBA工作目录,20G
/tran/myback	备份目录(远程NAS盘),容量要大于数据库的3.5倍(保留一个星期的备份,2全备+6增备,备份成功后删除7天之前的备份,所以最多可能有3全备+6增备)

10.250.112.99上的相关文件系统如下:
/dev/mapper/centos-tran_mysql    10G   33M   10G   1% /tran/mysql
/dev/mapper/centos-tran_app      20G   33M   20G   1% /tran/app
/dev/mapper/centos-tran_my3306  600G   33M  600G   1% /tran/my3306
/dev/mapper/centos-tran_myback  2.4T   33M  2.4T   1% /tran/myback

安装Mysql

新建用户和组并修改目录权限:
groupadd -g 500 mysql
useradd -g 500 -u 500 -d /home/mysql -m mysql
修改相关文件系统owner为mysql
chown mysql:mysql /tran/mysql /tran/app /tran/my3306 /tran/myback
放开mysql用户打开文件数限制(否则max_connections,table_open_cache参数将不能生效):
vi /etc/security/limits.conf
mysql soft nproc 65535
mysql hard nproc 65535
mysql soft nofile 65535
mysql hard nofile 65535
下载软件(后面都用mysql用户操作):
su - mysql
cd /tran/mysql/
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.30-el7-x86_64.tar.gz
tar -zxvf mysql-5.7.30-el7-x86_64.tar.gz
mv mysql-5.7.30-el7-x86_64 mysql-5.7.30
新建数据目录和日志目录,临时目录:
mkdir -p /tran/my3306/data
mkdir -p /tran/my3306/log
mkdir -p /tran/my3306/tmp
设置mysql用户path路径和简化登录方法:
vi .bash_profile
PATH=$PATH:$HOME/.local/bin:$HOME/bin:/tran/mysql/mysql-5.7.30/bin/
export PS1='[\u@\h `pwd`]$ '
alias m='mysql --login-path=root'
alias md='cd /tran/my3306/data'
alias ml='cd /tran/my3306/log/'

初始化数据库:

以下面的附件为模板,生成配置文件/tran/my3306/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# 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
#
# 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
character-set-server=utf8mb4
socket=/tran/my3306/mysql3306.sock

#设置从库复制进程不随数据库的启动而启动
#skip-slave-start
#从库复制参数
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery=ON
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# The TCP/IP Port the MySQL Server will listen on
port=3306

datadir=/tran/my3306/data
log-error=/tran/my3306/log/error.log
pid-file=/tran/my3306/data/mysql.pid
tmpdir=/tran/my3306/tmp
#导入导出文件存放目录
secure_file_priv='/tran/app/'
server_id=66

log-bin=/tran/my3306/log/binlog
binlog_cache_size = 10M
binlog_format=mixed
max_binlog_size=300M
expire_logs_days=7
enforce-gtid-consistency=on
gtid_mode=on
relay_log=/tran/my3306/log/mysql-relay-bin
log_slave_updates=1
#如果是备库则打开只读
#read_only=1

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

transaction_isolation=READ-COMMITTED
#表名存为小写,使用时大小写不敏感
lower_case_table_names = 1
#非super用户可以创建函数
log_bin_trust_function_creators=on

slow_query_log=1
long_query_time=1
slow_query_log_file=/tran/my3306/log/slow.log
slow_launch_time=5

innodb_buffer_pool_size=48G
#当数据库大时需调整,否则会备份失败
#innodb_log_files_in_group=5
innodb_log_file_size=48M
max_connections=3000
table_open_cache=3000
初始化数据库(root用户执行,最后一行是数据库用户root的临时密码,需登录后修改):
/tran/mysql/mysql-5.7.30/bin/mysqld --initialize --user=mysql --basedir=/tran/mysql/ --datadir=/tran/my3306/data --explicit_defaults_for_timestamp
[root@Fit2cloud data]# /tran/mysql/mysql-5.7.30/bin/mysqld --initialize --user=mysql --basedir=/tran/mysql/ --datadir=/tran/my3306/data --explicit_defaults_for_timestamp
2020-05-15T07:04:35.478562Z 0 [ERROR] Can't find error-message file '/tran/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
2020-05-15T07:04:35.853976Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-05-15T07:04:35.919044Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-05-15T07:04:35.981098Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 55ea3712-967a-11ea-aa75-0050569cae61.
2020-05-15T07:04:35.983004Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-05-15T07:04:35.983984Z 1 [Note] A temporary password is generated for root@localhost: edA_Cb740sps
这个命令还会生成配置文件/etc/my.cnf,我们不需要,可以将其删除:rm /etc/my.cnf

启动数据库:

mysql用户:
启动mysql
mysqld_safe --defaults-file=/tran/my3306/my.cnf &
登陆mysql
mysql -uroot -pedA_Cb740sps --socket=/tran/my3306/mysql3306.sock
修改root密码:
SET PASSWORD = PASSWORD('trAn2h7P');
flush privileges;
exit

验证

使用新密码登录数据库:
mysql -uroot -ptrAn2h7P --socket=/tran/my3306/mysql3306.sock
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

查看datadir结构:
[mysql@Fit2cloud my3306]$ pwd
/tran/my3306
[mysql@Fit2cloud my3306]$ tree
├── data
│   ├── auto.cnf
│   ├── ca-key.pem
│   ├── ca.pem
│   ├── client-cert.pem
│   ├── client-key.pem
│   ├── ib_buffer_pool
│   ├── ibdata1
│   ├── ib_logfile0
│   ├── ib_logfile1
│   ├── ibtmp1
│   ├── mysql
│   │   ├── columns_priv.frm
......
│   │   └── user.MYI
│   ├── mysql.pid
│   ├── performance_schema
│   │   ├── accounts.frm
......
│   │   └── variables_by_thread.frm
│   ├── private_key.pem
│   ├── public_key.pem
│   ├── server-cert.pem
│   ├── server-key.pem
│   └── sys
│       ├── db.opt
......
│       └── x@0024waits_global_by_latency.frm
├── log
│   ├── binlog.000001
│   ├── binlog.index
│   ├── error.log
│   └── slow.log
├── my.cnf
├── mysql3306.sock
└── mysql3306.sock.lock
└── tmp

设置本地免密登录

为了以后维护方便,设置本地免密登录,这样不用在维护脚本中写入用户名和密码,方法如下:
执行命令:
mysql_config_editor set -G root -S /tran/my3306/mysql3306.sock -u root -p
验证:
[mysql@edw-tools-master ~]$ mysql_config_editor print --all
[root]
user = root
password = *****
socket = /tran/my3306/mysql3306.sock
之后就可以这样登录了(由于上面设置了alias,可以直接用“m”登录):
mysql --login-path=root
等同于:
mysql -uroot -ptrAn2h7P --socket=/tran/my3306/mysql3306.sock

配置mysql开机自启动

root用户执行:
chmod +x /etc/rc.d/rc.local

vi /etc/rc.d/rc.local
su - mysql -c "mysqld_safe --defaults-file=/tran/my3306/my.cnf &"

 

版权声明
本文为[lyle_luo]所创,转载请带上原文链接,感谢
https://my.oschina.net/lyleluo/blog/4836609

Tags linux Mysql
Scroll to Top