I. MySQL database introduction 1, Database basic knowledge
MySQL is an open-source relational database management system (RDBMS) that uses the most common database management language – Structured Query Language (SQL) for database management.
MySQL was acquired by Sun in 2008, and in 2009 Sun was acquired by Oracle Corporation.
MySQL5.6 function changes greatly, MySQL5.7 performance is greatly improved
MARIADB is a branch of MySQL, the official website: https://mariadb.com, MARIADB is mainly maintained by Skysql company, and the Skysql company team is made up of most of the original classes by MySQL original author.
- MySQL version is divided into Community Community Edition, Enterprise Business Edition, GA (generally Available) through version (usually used in production environment), DMR (development Milestone release) Development of the milestone release, RC (Release Candidate) Release Candidate, beta Open beta, and alpha internal beta version.
A database is a warehouse that organizes, stores, and manages data according to its structure, which is generated more than more than 60 years ago, with the development of information technology and markets, especially after the 1990s, data management is no longer just a way to store and manage data, but to transform it into the kind of data management that users need. There are many types of databases, ranging from the simplest tables with various data to large database systems that can store massive amounts of data, are widely used in all aspects.
The main databases are: Sqlserver,mysql,oracle, SQLite, Access, MS SQL Server, etc., this article mainly describes the MySQL
2, the function of database management
A. Saving data to a file or memory
B. Receive a specific command, and then perform the appropriate action on the file
Note: For the database management system, do not need to create files and folders themselves, but instead of directly passing commands to the corresponding software, let them for file operations, they are collectively referred to as Database management system (Dbms,database Management Systems)
Second, MySQL installation configuration 1. Several common installation package methods for MySQL
a.rpm Install # #不能定义安装路径, the default is to install the/usr/directory below
B. Source code Installation
C. Binary free Compile install # # # #不用编译, you can specify the installation directory
Description: Generally in the work of the recommended use of binary compiler-free installation
2. Installation procedure
cd /usr/local/src/ ####指定mysql的安装目录,其他目录也行wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz
Unpacking the installation package
tar zxvf mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz
Move Rename
mv mysql-5.6.36-linux-glibc2.5-x86_64 /usr/local/mysqlcd /usr/local/mysql/lsbin COPYING data docs include lib man mysql-test README scripts share sql-bench support-files
Create a MySQL user
useradd mysql
Create a directory that holds MySQL data
[[email protected] mysql]# mkdir /data [[email protected] mysql]# ls /data/[[email protected] mysql]#[[email protected] mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql
Note: After executing this command, how to verify the success of this initialization command, can be confirmed from two points
1) After executing the command, there will be two OK at the end
[[email protected] mysql]#./scripts/mysql_install_db--user=mysql--datadir=/data/mysqlinstalling MySQL System Tables ... 2017-11-29 06:16:59 0 [Warning] TIMESTAMP with implicit the DEFAULT value is deprecated. --explicit_defaults_for_timestamp server option (see documentation for more details). 2017-11-29 06:16:59 0 [not E] Ignoring--secure-file-priv value as server is running with--bootstrap.2017-11-29 06:16:59 0 [Note]./bin/mysqld (mysq LD 5.6.36) Starting as Process 6626 ... 2017-11-29 06:16:59 6626 [note] innodb:using Atomics to ref count buffer pool pages2017-11-29 06:16:59 6626 [note] InnoDB : The InnoDB memory heap is disabled2017-11-29 06:16:59 6626 [Note] innodb:mutexes and rw_locks use GCC atomic BUILTINS20 17-11-29 06:16:59 6626 [note] innodb:memory barrier is not used2017-11-29 06:16:59 6626 [note] innodb:compressed tables Use zlib 1.2.32017-11-29 06:16:59 6626 [note] innodb:using Linux native aio2017-11-29 06:16:59 6626 [note] Innodb:using CPU CRC32 Instructions2017-11-29 06:16:59 6626 [note] innodb:initializing buffer pool, size = 128.0m2017-11-29 06:16:59 6626 [note] InnoDB: Completed initialization of buffer pool2017-11-29 06:16:59 6626 [Note] innodb:the first specified data file./ibdata1 di D not exist:a new database to is created!2017-11-29 06:16:59 6626 [Note] innodb:setting file./ibdata1 size to MB2017 -11-29 06:16:59 6626 [Note] Innodb:database physically writes the file full:wait ... 2017-11-29 06:16:59 6626 [note] innodb:setting log file./ib_logfile101 size to ~ mb2017-11-29 06:17:00 6626 [note] Inno db:setting log file./ib_logfile1 size to mb2017-11-29 06:17:00 6626 [Note] innodb:renaming log file./ib_logfile101 To./ib_logfile02017-11-29 06:17:00 6626 [Warning] innodb:new log files created, lsn=457812017-11-29 06:17:00 6626 [Note] Innodb:doublewrite Buffer not found:creating new2017-11-29 06:17:00 6626 [Note] innodb:doublewrite buffer created2017- 11-29 06:17:00 6626 [Note] innodb:128 rollback segment (s) ARe active.2017-11-29 06:17:00 6626 [Warning] innodb:creating FOREIGN KEY constraint system tables.2017-11-29 06:17:00 6626 [Note] Innodb:foreign key constraint system tables created2017-11-29 06:17:00 6626 [Note] innodb:creating tablespace and Datafi Le system tables.2017-11-29 06:17:00 6626 [Note] innodb:tablespace and datafile system tables created.2017-11-29 06:17:00 6626 [note] innodb:waiting for purge to start2017-11-29 06:17:00 6626 [note] innodb:5.6.36 started; Log sequence number 02017-11-29 06:17:00 6626 [note] Binlog end2017-11-29 06:17:00 6626 [note] innodb:fts optimize thread exiting.2017-11-29 06:17:00 6626 [Note] innodb:starting shutdown ... 2017-11-29 06:17:02 6626 [Note] Innodb:shutdown completed; Log sequence number 1625977OKFilling help tables ... 2017-11-29 06:17:02 0 [Warning] TIMESTAMP with implicit the DEFAULT value is deprecated. --explicit_defaults_for_timestamp server option (see documentation for more details). 2017-11-29 06:17:02 0 [not E] ignoring--Secure-file-priv value as server is running with--bootstrap.2017-11-29 06:17:02 0 [Note]./bin/mysqld (mysqld 5.6.36) STA Rting as Process 6652 ... 2017-11-29 06:17:02 6652 [note] innodb:using Atomics to ref count buffer pool pages2017-11-29 06:17:02 6652 [note] InnoDB : The InnoDB memory heap is disabled2017-11-29 06:17:02 6652 [Note] innodb:mutexes and rw_locks use GCC atomic BUILTINS20 17-11-29 06:17:02 6652 [note] innodb:memory barrier is not used2017-11-29 06:17:02 6652 [note] innodb:compressed tables Use zlib 1.2.32017-11-29 06:17:02 6652 [note] innodb:using Linux native aio2017-11-29 06:17:02 6652 [note] Innodb:using CPU CRC32 instructions2017-11-29 06:17:02 6652 [Note] innodb:initializing buffer pool, size = 128.0m2017-11-29 06:17:02 6 652 [note] innodb:completed initialization of buffer pool2017-11-29 06:17:02 6652 [note] Innodb:highest supported file F Ormat is barracuda.2017-11-29 06:17:02 6652 [Note] innodb:128 rollback segment (s) was active.2017-11-29 06:17:02 6652 [NOTE] innodb:waiting for purge to start2017-11-29 06:17:02 6652 [Note] innodb:5.6.36 started; Log sequence number 16259772017-11-29 06:17:02 6652 [note] Binlog end2017-11-29 06:17:02 6652 [note] Innodb:fts optimize Thread exiting.2017-11-29 06:17:02 6652 [Note] innodb:starting shutdown ... 2017-11-29 06:17:04 6652 [Note] Innodb:shutdown completed; Log sequence number 1625987OKTo start mysqld at boot time with Copysupport-files/mysql.server to the right place fo R your systemplease REMEMBER to SET A PASSWORD for the MySQL root USER! To does, start the server, then issue the following commands:./bin/mysqladmin-u root password ' new-password './bin/my Sqladmin-u root-h localhost.localdomain password ' new-password ' Alternatively you can run:./bin/mysql_secure_installat Ionwhich would also give you the option of removing the testdatabases and anonymous user created by default. This isstrongly recommended for production servers. See the Manual for more instructions. You can startThe MySQL daemon with:cd. ;./bin/mysqld_safe &you can test the MySQL daemon with mysql-test-run.pl CD mysql-test; Perl mysql-test-run.plplease Report No problems at Http://bugs.mysql.com/The latest information on MySQL is available On the web Athttp://www.mysql.comsupport MySQL by buying support/licenses at Http://shop.mysql.comNew Default Config file was created as./my.cnf Andwill is used by default if you start it. Settingswarning:default config file/etc/my.cnf exists on the systemthis file wil L be read by default by the MySQL Serverif you does not want to use this, either remove it, or use The--defaults-file Argume NT to Mysqld_safe when starting the server
2) Confirm that the command performs a successful method
[[email protected] mysql]# echo $?0说明命令初始化成功
3. Copy the MySQL configuration file and startup script
[[email protected] mysql]# lsbin data include man mysql-test scripts Sql-bench COPYING docs lib M Y.CNF README share support-files[[email protected] mysql]# ls support-files/my-default.cnf # # # #mysql的启动模板配置文 Piece support-files/my-default.cnf[[email protected] mysql]# cat! $cat support-files/my-default.cnf# for advice on how To change settings see# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html# * * * does not EDIT this FILE. It's a template which'll be copied to the# * * * default location during install, and would be replaced if you# * * * Upgrade To a newer version of MySQL. [mysqld]# Remove leading # and set to the amount of RAM for the most important data# cache in MySQL. Start at 70% of all RAM for dedicated server, else 10%.# innodb_buffer_pool_size = 128m# Remove Leading # to turn on a V ery important data Integrity option:logging# changes to the binary log between backups.# log_bin# These is commonly set, RemoveThe # and set as required.# Basedir = ..... # DataDir = ... # ... # port = ... ... # server_id = ... ... # socket = .... # Remove Lead ING # to set options mainly useful for reporting servers.# the server defaults is faster for transactions and fast SELECT s.# Adjust sizes as needed, experiment to find the optimal values.# join_buffer_size = 128m# sort_buffer_size = 2m# Read_r nd_buffer_size = 2msql_mode=no_engine_substitution,strict_trans_tables copy mysql boot configuration file [[email protected] MySQL] # CP SUPPORT-FILES/MY-DEFAULT.CNF/ETC/MY.CNF See which rpm the/ETC/MY.CNF is installed in [[email protected] mysql-5.6.36-linux-glibc2.5-x86_64]# rpm-qf/etc/my.cnfmariadb-libs-5.5.52-1.el7.x86_64
Edit the/ETC/MY.CNF configuration file, modify the DataDir, socket parameters to
[mysqld]datadir=/data/mysqlsocket=/tmp/mysql.sock
MySQL Data default startup script is Mysql.server
[[email protected] mysql]# ls support-files/binary-configure magic my-default.cnf mysqld_multi.server mysql-log-rotate mysql.server[[email protected] mysql]# ls support-files/mysql.server support-files/mysql.server
Configure MySQL database startup script parameters, edit support-files/mysql.server, change the basedir and DataDir parameters to
basedir=/usr/local/mysqldatadir=/data/mysql
Start the MySQL database and verify that the process exists
[[email protected] mysql]# systemctl start mysql[[email protected] mysql]# systemctl status MySQL Mysqld.service-lsb:start and Stop MySQL loaded:loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset:disabled) Acti Ve:active (running) since Mon 2018-03-19 07:25:05 CST; 9s ago Docs:man:systemd-sysv-generator (8) process:20142 execstop=/etc/rc.d/init.d/mysqld Stop (code=exited, status= 0/success) process:21328 execstart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/success) Memory:436.5m CGro Up:/system.slice/mysqld.service├─21336/bin/sh/usr/local/mysql/bin/mysqld_safe--datadir=/data/mysql--pid-fi Le=/data/mysql/centos7.pid└─21474/usr/local/mysql/bin/mysqld--basedir=/usr/local/mysql--datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin--user= ... Mar 07:25:03 Centos7 systemd[1]: Starting Lsb:start and stop MySQL ... Mar 07:25:03 Centos7 mysqld[21328]: Starting mysql.logging to '/data/mysql/centos7.err '. Mar 19 07:25:05 Centos7 mysqld[21328]:. success! Mar 07:25:05 centos7 systemd[1]: Started lsb:start and stop MySQL. See if the MySQL process exists [[email protected] mysql]# PS aux |grep mysqlroot 21336 0.0 0.0 115392 1700? S 07:25 0:00/bin/sh/usr/local/mysql/bin/mysqld_safe--datadir=/data/mysql--pid-file=/data/mysql/centos7.pidmysql 21474 7.5 0.3 1302864 452224? Sl 07:25 0:01/usr/local/mysql/bin/mysqld--basedir=/usr/local/mysql--datadir=/data/mysql--plugin-dir=/usr/local/ Mysql/lib/plugin--user=mysql--log-error=/data/mysql/centos7.err--pid-file=/data/mysql/centos7.pid--socket=/tmp /mysql.sockroot 21511 0.0 0.0 112664 968 PTS/7 s+ 07:25 0:00 grep--color=auto MySQL database 3306 port is being monitored [[email&n Bsp;protected] mysql]# netstat-nlp |grep 3306tcp6 0 0::: 3306:::* LISTEN 21474/mysqld [[email protected] mysql]#
Iii. Skills Experience Summary The use of killall and kill commands in daily operation and maintenance work
Killall is to kill all running processes, if you are writing data to MySQL, if you cannot kill a process with the KILL command, indicating that the amount of data at this time is large, then in this case, can not use kill-9 to kill the process, Because this causes the data that is being written to disk or cached (memory) to not be synced to disk, you may lose some data, which can only be used with killall.
If the process of a service cannot be killed, it indicates that the data volume of the service is very large, and only when the service data is synchronized can it kill the process.
Install MySQL summary on CentOS7