MySQL production environment level deployment

Download and Extract

Many companies still use MySQL5.6, so this article will continue to use it as an example. Download address, share code: PVVC Install MySQL in /usr/local.

Switch to the installation path
[root@hadoop001 ~]# cd /usr/local

# check if MySQL is installed before
[root@hadoop001 local]# ps -ef|grep mysqld
root      2493  2423  0 19:48 pts/3    00:00:00 grep mysqld
[root@hadoop001 local]# rpm -qa |grep -i mysql

Unzip and create soft links
[root@hadoop001 local]# tar XZVF mysql - 5.6.23 - Linux - glibc2.5 - x86_64. Tar. Gz
[root@hadoop001 local]-s /usr/local/mysql-5.6.23-linux-glibc2.5-x86_64 /usr/local/mysql
Copy the code

See my previous article on why soft links are created for a detailed description of soft link usage scenarios. This is one of them.

Create MySQL users and user groups

Create a dedicated MySQL user to prevent other users from misoperating the database and causing database problems. Permission control is very important in production.

Create a dba user group
[root@hadoop001 local]# groupadd -g 101 dba

Mysqladmin = /usr/local/mysql; /usr/local/mysql
[root@hadoop001 local]# useradd -u 514 -g dba -G root -d /usr/local/mysql mysqladmin
[root@hadoop001 local]# id mysqladmin
uid=514(mysqladmin) gid=101(dba) groups=101(dba),0(root)

# give mysqladmin user password
[root@hadoop001 local]# passwd mysqladmin

Copy the environment variable configuration file to the home directory of the mysqladmin user
[root@hadoop001 local]# cp /etc/skel/.* /usr/local/mysql/

Copy the code

Because the user uses the soft link as his home directory, there is no personal environment variable profile (i.e. For those of you who are not quite sure, see the user/user group command set section in Linux Basics series 2 for details on how to use this operation.

Create the MySQL configuration file

The configuration file we used is in the /etc/my.conf directory. If you have this file on your system, please empty it and copy the following contents into the file.

[client]
port            = 3306
socket          = /usr/local/mysql/data/mysql.sock
 
[mysqld]
port            = 3306
socket          = /usr/local/mysql/data/mysql.sock skip-external-locking key_buffer_size = 256M sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 4M query_cache_size= 32M max_allowed_packet = 16M myisam_sort_buffer_size=128M tmp_table_size=32M  table_open_cache = 512 thread_cache_size = 8 wait_timeout = 86400 interactive_timeout = 86400 max_connections = 600# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 32

#isolation level and default engine 
default-storage-engine = INNODB
transaction-isolation = READ-COMMITTED

server-id  = 1
basedir     = /usr/local/mysql
datadir     = /usr/local/mysql/data
pid-file     = /usr/local/mysql/data/hostname.pid

#open performance schema
log-warnings
sysdate-is-now

binlog_format = MIXED
log_bin_trust_function_creators=1
log-error  = /usr/local/mysql/data/hostname.err
log-bin=/usr/local/mysql/arch/mysql-bin
#other logs
#general_log =1
#general_log_file = /usr/local/mysql/data/general_log.err
#slow_query_log=1
#slow_query_log_file=/usr/local/mysql/data/slow_log.err

#for replication slave
#log-slave-updates 
#sync_binlog = 1

#for innodb options 
innodb_data_home_dir = /usr/local/mysql/data/
innodb_data_file_path = ibdata1:500M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/arch innodb_log_files_in_group = 2 innodb_log_file_size = 200M innodb_buffer_pool_size = 2048M <-- If your server has less than or equal to 4 gigabytes of memory, Set it to 1024M innodb_additional_mem_pool_size = 50M Innodb_log_buffer_size = 16M Innodb_lock_WAIT_TIMEOUT = 100#innodb_thread_concurrency = 0
innodb_flush_log_at_trx_commit = 1
innodb_locks_unsafe_for_binlog=1

# InnoDB IO features: Add for mysql5.5.8
performance_schema
innodb_read_io_threads=4
innodb-write-io-threads=4
innodb-io-capacity=200
#purge threads change default(0) to 1 for purge
innodb_purge_threads=1
innodb_use_native_aio=on

#case-sensitive file names and separate tablespace
innodb_file_per_table = 1
lower_case_table_names=1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[mysqlhotcopy]
interactive-timeout

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
Copy the code

The default configuration file reading sequence is: CNF ->SYSCONFDIR/my.cnf->$MYSQL_HOME/my.cnf-> –defaults-extra-file->~/my.cnf ->$MYSQL_HOME/my.cnf-> $MYSQL_HOME/my.cnf-> –defaults-extra-file->~/my.cnf So we use /etc/my.conf.

File permission Change

# Change the permission and owning user of the configuration file
[root@hadoop001 local]# chown mysqladmin:dba /etc/my.cnf
[root@hadoop001 local]# chmod 640 /etc/my.cnf

# change the permission of the home directory and the owner of the user, after the modification is complete, make sure to CD into, ll again check
[root@hadoop001 local]# chown -R mysqladmin:dba /usr/local/mysql
[root@hadoop001 local]# chown -R mysqladmin:dba /usr/local/mysql/*
[root@hadoop001 local]# chmod -R 755 /usr/local/mysql 
[root@hadoop001 local]# chmod -R 755 /usr/local/mysql/* 
Copy the code

MySQL installation

# Switch user
[root@hadoop001 local]# su - mysqladmin 
[mysqladmin@hadoop001 ~]$ pwd
/usr/local/mysql

Create a folder for storing logs
[mysqladmin@hadoop001 ~]$ mkdir arch 

Check whether the system is installed
[mysqladmin@hadoop001 ~]$ yum -y install autoconf
[mysqladmin@hadoop001 ~]$ yum -y install libaio

# Start installation
[mysqladmin@hadoop001 ~]$ scripts/mysql_install_db  \
--user=mysqladmin \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data 
Copy the code

Configure the MySQL service and automatic startup upon startup

[root@hadoop001 local]# cd /usr/local/mysql
Copy the service file to init.d and rename it mysql
[root@hadoop001 mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysql 
# Grant executable permission
[root@hadoop001 mysql]# chmod +x /etc/rc.d/init.d/mysql
# delete service
[root@hadoop001 mysql]# chkconfig --del mysql
# add service
[root@hadoop001 mysql]# chkconfig --add mysql
[root@hadoop001 mysql]# chkconfig --level 345 mysql on
[root@hadoop001 mysql]# vi /etc/rc.local
#! /bin/sh
#
# This script will be executed *after* all the other init scripts.
# You can put your own initialization stuff in here if you don't
# want to do the full Sys V style init stuff.

touch /var/lock/subsys/local

su - mysqladmin -c "/etc/init.d/mysql start"<-- Add this lineCopy the code

Start the MySQL service and verify that it is running

# Switch user
[root@hadoop001 mysql]# su - mysqladmin
[mysqladmin@hadoop001 ~]$ pwd
/usr/local/mysql

# Delete your own configuration file to avoid future misunderstandings
[mysqladmin@hadoop001 ~]$ rm -rf my.cnf

# MYSQL running[mysqladmin@hadoop001 ~]$mysqLD_safe & <-- this command may not end and does not have much impactCheck whether the MySQL process exists
[mysqladmin@hadoop001 ~]$ ps -ef|grep mysqld
514       6247  6219  0 17:30 pts/1    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe
514       6902  6247  2 17:30 pts/1    00:00:01 /usr/local/mysql/bin/mysqld --
basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-
dir=/usr/local/mysql/lib/plugin --log-error=/usr/local/mysql/data/hostname.err --pid-
file=/usr/local/mysql/data/hostname.pid --socket=/usr/local/mysql/data/mysql.sock --
port=3306
514       6927  6219  0 17:31 pts/1    00:00:00 grep mysqld

Check whether MySQL port is 3306
[mysqladmin@hadoop001 ~]$ netstat -tulnp | grep 6902
tcp        0      0 :::3306                     :::*          LISTEN      11541/mysqld   

# check the MySQL service
[root@shadoop001 local]# service mysql status
MySQL running (21507)                                      [  OK  ]
Copy the code

If the MySQL does not start, check the error in the /data/hostname.error file in the MySQL decompression directory, and then troubleshoot the problem.

Log in MySQL

[mysqladmin@hadoop001 ~]$ mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.23- Log MySQL Community Server (GPL) Copyright (C) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type'help; ' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)
mysql> use mysql；
Database changed
# Change the root password
mysql> update user set password=password('password') where user='root';
Query OK, 4 rows affected (0.00 sec)
MySQL > select * from user where username and password do not exist
mysql> delete from user where user=' '; Query OK, 2 rows affected (0.00 SEC) mysql> select host,user,password from user; +----------------+------+-------------------------------------------+ | host | user | password | +----------------+------+-------------------------------------------+ | localhost | root | * 6340 be3c15d246b0d74baf3f135915ed19e0069f | | hadoop001 | root | * 6340 be3c15d246b0d74baf3f135915ed19e0069f | | 127.0.0.1  | root | *6340BE3C15D246B0D74BAF3F135915ED19E0069F | | ::1 | root | *6340BE3C15D246B0D74BAF3F135915ED19E0069F | +----------------+------+-------------------------------------------+ 4 rowsin set (0.00 sec)
# refresh user permissions
mysql> flush privileges;
Copy the code

Configure global environment variables

[root@hadoop001 ~]# vi /etc/profile
# /etc/profile

# System wide environment and startup programs, for login setup
# Functions and aliases go in /etc/bashrc

# It's NOT a good idea to change this file unless you know what you
# are doing. It's much better to create a custom.sh shell script in
# /etc/profile.d/ to make custom changes to your environment, as this
# will prevent the need for merging in future updates..for i in /etc/profile.d/*.sh /etc/profile.d/sh.local ; do
    if [ -r "$i" ]; then
        if [ "${-#*i}"! ="$-" ]; then
            . "$i"
        else
            . "$i" >/dev/null
        fi
    fi
done

unset i
unset -f pathmunge


MYSQL_HOME=/usr/local< - new/mysqlexport MYSQL_HOME

PATH=${MYSQL_HOME}/bin:$PATH< - newexport PATH

Global environment variables take effect
[root@hadoop001 ~]# source /etc/profile
Copy the code

Configure the mysqladmin user environment variable (optional)

[root@hadoop001 ~]# su - mysqladmin
[mysqladmin@hadoop001 ~]$ vi ~/.bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
	. ~/.bashrc
fi

# add the following
unset USERNAME

#stty erase ^H
set umask to 022
umask 022
PS1=`uname -n`":"'$USER'":"'$PWD'": >"; export PS1

# Personal environment variables in effect
[mysqladmin@hadoop001 ~]$ . ~/.bash_profile
Copy the code

The above Settings are only for changing the user, so that you do not need to use the PWD command. Here is an example:

[root@hadoop001 ~]# su - mysqladmin
Last login: Mon Jun 24 22:11:23 CST 2019 on pts/1
hadoop001:mysqladmin:/usr/local/mysql:>
Copy the code