Shulou(Shulou.com)05/31 Report--

This article mainly introduces the commands commonly used by linux in DB operation and maintenance, which have a certain reference value, and interested friends can refer to them. I hope you can learn a lot after reading this article.

# cat / etc/issue

CentOS release 6.5 (Final)

Kernel\ r on an\ m

The test machine I use is CentOS 6.5. at present, the popular linux distributions are:

Red Hat http://www.redhat.com (currently the best version of the enterprise version, but charged for commercial services, responsible for operating system security)

Fedora http://fedoraproject.org (the development version of Red Hat with fast updates)

Mandriva http://mandriva.com

Novell SuSE http://novell.com/linux

Debian http://debian.org

Slackware http://slackware.com

Gentoo http://gentoo.org (better performance)

Ubuntu http://ubuntu.com (based on Debian)

CentOS http://centos.org (free server version, meaning professional pirated Red Hat, remember professional)

Ubuntu Kylin http://www.ubuntukylin.com (domestic version, based on Ubuntu, now 16.04 seems very friendly)

Deepin https://www.deepin.org/ (the first Debian-based system in China, community support is quite friendly and released a lot of linux versions of domestic software, Ubuntu and Ubuntu Kylin are the beneficiaries, this sentiment is full score)

Secondly, we must take a look at the hardware information such as CPU, hard disk size and memory.

View CPU information (model number)

# cat / proc/cpuinfo | grep name | cut-f2-d: | uniq-c

4 Intel (R) Xeon (R) CPU E5-2420 0 @ 1.90GHz E5410 @ 2.33GHz

(see that there are 8 logical CPU and also know the CPU model)

# cat / proc/cpuinfo | grep physical | uniq-c

4 physical id: 0

4 physical id: 1

(which means it's actually two 4-core CPU)

# getconf LONG_BIT

sixty-four

(indicates that CPU is running in 32bit mode, but it does not mean that CPU does not support 64bit)

# cat / proc/cpuinfo | grep flags | grep'lm'| wc-l

four

(the result is greater than 0, indicating that 64bit computing is supported. Lm refers to long mode, while supporting lm means 64bit)

Check the machine model

# dmidecode | grep "Product Name"

View network card information

# dmesg | grep-I eth

# df-lh

Filesystem Size Used Avail Use% Mounted on

/ dev/mapper/VolGroup-lv_root 50G 7.2G 40G 16% /

Tmpfs 1.9G 68K 1.9G 1% / dev/shm

/ dev/sda1 485m 40m 421m 9% / boot

/ dev/mapper/VolGroup-lv_home 49G 7.8G 39G 17% / home

At present, most of the CPU on the market are x86 and PowerPC, and specific machines may use Sun's SPARC series.

CPU contains a set of microinstructions, and different sets of microinstructions lead to the advantage of CPU efficiency.

Frequency of CPU: the number of jobs that CPU can do per second

The CPU frequency 3.0G means that the CPU can work 3.0 * 10 ^ 9 times in a second.

Therefore, the high-frequency CPU performance is not necessarily good, but also depends on the micro-instruction set.

When you get the machine, the most important thing in the end is to have a network, otherwise you can't play it.

Ifconfig

Eth0 Link encap:Ethernet HWaddr 00:50:56:B3:6B:6D

Inet addr:172.15.11.73 Bcast:172.15.11.255 Mask:255.255.255.0

Inet6 addr: fe80::250:56ff:feb3:6b6d/64 Scope:Link

UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

RX packets:40487206 errors:0 dropped:0 overruns:0 frame:0

TX packets:791120 errors:0 dropped:0 overruns:0 carrier:0

Collisions:0 txqueuelen:1000

RX bytes:5193260758 (4.8GiB) TX bytes:296331233 (282.6 MiB)

Linux sets static IP

Cd / etc/sysconfig/network-scripts/

# vim ifcfg-eth0

DEVICE=eth0

HWADDR=00:50:56:B0:07:B6

TYPE=Ethernet

UUID=ceb36fa0-487e-4f8a-bba1-9f94728086e5

ONBOOT=yes

NM_CONTROLLED=yes

BOOTPROTO=static

IPADDR=192.168.6.203

NETMASK=255.255.255.0

GATEWAY=192.168.6.2

DNS1=192.168.0.5

Linux uninstalls excess startup items

The first method

1. Start by listing the kernels in use on the system:

# uname-a

two。 Query all kernels in the system:

# rpm-qa | grep kernel

3. Delete the kernel you want to delete: (for example, on my system, if I want to delete the kernel of kernel-2.6.32-279.9.1.el6.x86_64, I need to delete all the kernels that contain the words kernel-2.6.32-279.9.1.el6.x86_64)

# yum remove kernel-2.6.32-279.9.1.el6.x86_64

4. After rebooting, you can see that the kernel has been deleted and the extra startup items have been deleted automatically.

The second method

Manually modify / boot/grub/menu.lst to delete excess items.

Linux refresh memory

Clear the page cache

# sync; echo 1 > / proc/sys/vm/drop_caches

Clear catalog entries and inode

# sync; echo 2 > / proc/sys/vm/drop_cachesc

Clear page bold directory entries and inode

# sync; echo 3 > / proc/sys/vm/drop_caches

Swap cleanup

Swapoff-a & & swapon-a

After doing the above, let's say that we are going to complete the task of compiling and installing percona.

The first step is to download to the latest installation package

# cd / home

# wget http://jaist.dl.sourceforge.net/project/boost/boost/1.59.0/boost_1_59_0.tar.gz

# wget xxxx.cmake-3.3.2.tar.gz

# wget xxxx.percona-server-5.7.13-6.tar.gz

# wget xxxx/lib_mysqludf_sys-master.zip

Download so many installations, explain

MySQL currently requires boost_1_59_0

CMake 2.8.2 higher

These two are the rigid requirements for compiling percona-server-5.7.13-6, otherwise they will not be compiled.

Both CMake and boost_1_59_0 installation and compilation are simple.

The new release of CMake may already be very high, so you can check it before compiling and installing it. Newer versions than CMake 2.8.2 may not have to be installed.

# cmake-version

Cmake version 2.8.12.2

CMake:

#. / bootstrap

# make&&make install

Boost_1_59_0:

# tar-zxvf boost_1_59_0.tar.gz

# mv boost_1_59_0 / usr/local/boost

# cd / usr/local/boost

#. / bootstrap.sh

#. / b2

Boost_1_59_0 this hurt, this package seems to be very big, but the official version of the integration of boost_1_59_0 source code, percona does not seem to have.

Follow the steps written above, and then wait slowly. There is no progress bar in this compilation pit.

Gcc.compile.c++ bin.v2/libs/wave/build/gcc-4.4.7/release/link-static/threading-multi/cpplexer/re2clex/cpp_re.o

Gcc.archive bin.v2/libs/wave/build/gcc-4.4.7/release/link-static/threading-multi/libboost_wave.a

Common.copy stage/lib/libboost_wave.a

... failed updating 56 targets...

... skipped 6 targets...

... updated 1069 targets...

When this step is completed, it is proved that the installation of boost_1_59_0 has been completed, which is all preparatory work.

Now let's officially start the installation of percona-server-5.7.13-6

① Set timezone (set the time zone of the server: Shanghai, Asia)

# Set timezone (set the time zone of the server: Asia and Shanghai)

Rm-rf / etc/localtime

Ln-sf / usr/share/zoneinfo/Asia/Shanghai / etc/localtime

Ln-sf / usr/share/zoneinfo/America/New_York / etc/localtime (this is the time of the United States)

# ln-sf / usr/share/zoneinfo/America/New_York / etc/localtime

# date

Tue Aug 9 02:11:37 EDT 2016

# ln-sf / usr/share/zoneinfo/Asia/Shanghai / etc/localtime

# date

Tue Aug 9 14:11:47 CST 2016

② Remove MySQL if exists mysql (delete MySQL already exists on the server, remember database backup)

Service mysql stop

Rpm-qa | grep mysql

Rpm-e mysql

Yum-y remove mysql-server mysql mysql-libs

Because many machines may be installed with databases by yum when they get them, this should be dealt with in advance.

③ commonly used dependent installation

Centos:

Yum-y install patch make cmake gcc gcc-c++ gcc-g77 flex bison file libtool libtool-libs autoconf kernel-devel libjpeg libjpeg-devel libpng libpng-devel libpng10 libpng10-devel gd gd-devel freetype freetype-devel libxml2 libxml2-devel zlib zlib-devel glib2 glib2-devel bzip2 bzip2-devel libevent libevent-devel ncurses ncurses-devel curl curl-devel e2fsprogs e2fsprogs-devel krb5 krb5-devel libidn libidn-devel openssl openssl-devel vim-minimal nano fonts-chinese gettext gettext-devel ncurses-devel gmp-devel pspell-devel unzip libcap diffutils

Debian:

Yum-y install build-essential gcc gathers + make cmake autoconf automake re2c wget cron bzip2 libzip-dev libc6-dev file rcconf flex vim nano bison M4 gawk less make cpp binutils diffutils unzip tar bzip2 libbz2-dev libncurses5 libncurses5-dev libtool libevent-dev libpcre3 libpcre3-dev libpcrecpp0 libssl-dev zlibc openssl libsasl2-dev libxml2 libxml2-dev libltdl3-dev libltdl-dev libmcrypt-dev zlib1g zlib1g-dev libbz2- 1.0 libbz2-dev libglib2.0- 0 libglib2.0-dev libpng3 libfreetype6 libfreetype6-dev libjpeg62 libjpeg62-dev libjpeg-dev libpng-dev libpng12- 0 libpng12-dev curl Libcurl3 libmhash3 libmhash-dev libpq-dev libpq5 gettext libncurses5-dev libcurl4-gnutls-dev libjpeg-dev libpng12-dev libxml2-dev zlib1g-dev libfreetype6 libfreetype6-dev libssl-dev libcurl3 libcurl4-openssl-dev libcurl4-gnutls-dev mcrypt libcap-dev diffutils ca-certificates debian-keyring debian-archive-keyring

Ubuntu:

Apt-get-yf install build-essential gcc gaming + make cmake automake autoconf re2c wget cron bzip2 libzip-dev libc6-dev file rcconf flex vim nano bison M4 gawk less make cpp binutils diffutils unzip tar bzip2 libbz2-dev unrar p7zip libncurses5-dev libncurses5 libncurses5-dev libncurses5-dev libtool libevent-dev libpcre3 libpcre3-dev libpcrecpp0 libssl-dev zlibc openssl libsasl2-dev libltdl3-dev libltdl-dev libmcrypt-dev zlib1g zlib1g-dev libbz2- 1.0 libbz2-dev libglib2.0- 0 libglib2.0-dev libpng3 libjpeg62 libjpeg62-dev libjpeg-dev libpng-dev libpng12- 0 libpng12- Dev curl libcurl3 libmhash3 libmhash-dev libpq-dev libpq5 gettext libncurses5-dev libcurl4-gnutls-dev libjpeg-dev libpng12-dev libxml2-dev zlib1g-dev libfreetype6 libfreetype6-dev libssl-dev libcurl3 libcurl4-openssl-dev libcurl4-gnutls-dev mcrypt libcap-dev diffutils ca-certificates debian-keyring debian-archive-keyring

There are a lot of dependencies, not all of them will be used, but let's just update the software.

④ MySQL account creation

Groupadd mysql

Useradd-s / sbin/nologin-M-g mysql mysql

⑤ creates the MySQL database to store the directory, and the log directory should also be given to the disk separately, so that the pressure on the separation of Icano will be less, so it will be put together here.

Mkdir-p / home/database

Chown-R mysql:mysql / home/database

⑥ precompiled camke

# cmake. -DCMAKE_INSTALL_PREFIX=/usr/local/mysql\

-DSYSCONFDIR=/etc\

-DENABLED_PROFILING=1\

-DMYSQL_DATADIR=/home/database\

-DMYSQL_TCP_PORT=3306\

-DWITH_MYISAM_STORAGE_ENGINE=1\

-DWITH_PARTITION_STORAGE_ENGINE=1\

-DWITH_CSV_STORAGE_ENGINE=1\

-DWITH_INNOBASE_STORAGE_ENGINE=1\

-DWITH_EXTRA_CHARSETS=gbk,gb2312,utf8,ascii,utf8mb4\

-DDEFAULT_COLLATION=utf8_general_ci\

-DDEFAULT_CHARSET=utf8\

-DENABLED_LOCAL_INFILE=1\

-DWITH_EDITLINE=bundled\

-DWITH_ZLIB=system\

-DWITH_BOOST=/usr/local/boost

If there is an error before, clean the error compilation log first.

Make clean

Rm CMakeCache.txt

After completing this step, cmake

Cmake. -DCMAKE_INSTALL_PREFIX=/usr/local/mysql\

-DSYSCONFDIR=/etc\

-DENABLED_PROFILING=1\

-DMYSQL_DATADIR=/home/database\

-DMYSQL_TCP_PORT=3306\

-DWITH_MYISAM_STORAGE_ENGINE=1\

-DWITH_PARTITION_STORAGE_ENGINE=1\

-DWITH_CSV_STORAGE_ENGINE=1\

-DWITH_INNOBASE_STORAGE_ENGINE=1\

-DWITH_EXTRA_CHARSETS=gbk,gb2312,utf8,ascii,utf8mb4\

-DDEFAULT_COLLATION=utf8_general_ci\

-DDEFAULT_CHARSET=utf8\

-DENABLED_LOCAL_INFILE=1\

-DWITH_EDITLINE=bundled\

-DWITH_ZLIB=system\

-DWITH_BOOST=/usr/local/boost

-- INSTALL perconaserverclient.pc lib/pkgconfig

-- CMAKE_BUILD_TYPE: RelWithDebInfo

-- COMPILE_DEFINITIONS: _ GNU_SOURCE;_FILE_OFFSET_BITS=64;HAVE_CONFIG_H

-- CMAKE_C_FLAGS:-Wall-Wextra-Wformat-security-Wvla-Wwrite-strings-Wdeclaration-after-statement

-- CMAKE_CXX_FLAGS:-Wall-Wextra-Wformat-security-Wvla-Woverloaded-virtual-Wno-unused-parameter

-- CMAKE_C_FLAGS_RELWITHDEBINFO:-O3-D_FORTIFY_SOURCE=2-g-fabi-version=2-fno-omit-frame-pointer-fno-strict-aliasing-DDBUG_OFF

-- CMAKE_CXX_FLAGS_RELWITHDEBINFO:-O3-D_FORTIFY_SOURCE=2-g-fabi-version=2-fno-omit-frame-pointer-fno-strict-aliasing-DDBUG_OFF

-- Configuring done

-- Generating done

-- Build files have been written to: / home/percona-server-5.7.13-6

⑦ compilation

Start compilation and installation

Make-j`grep processor / proc/cpuinfo | wc-l` & & make install

After this 100% installation is complete,

Cp support-files/my-default.cnf / etc/my.cnf

Ln-sf / usr/local/mysql/lib/mysql / usr/lib/mysql

Ln-sf / usr/local/mysql/include/mysql / usr/include/mysql

Ln-sf / usr/local/mysql/bin/mysql / usr/bin/mysql

Ln-sf / usr/local/mysql/bin/mysqldump / usr/bin/mysqldump

Ln-sf / usr/local/mysql/bin/myisamchk / usr/bin/myisamchk

Ln-sf / usr/local/mysql/bin/mysqld_safe / usr/bin/mysqld_safe

There are so many links here, but also to avoid environmental variables.

Cp support-files/mysql.server / etc/init.d/mysql

Chmod 700 / etc/init.d/mysql

Chkconfig-add mysql

Chkconfig-level 35 mysql on

Go in and modify these two, and the rest is about the configuration file. I won't discuss it here for the time being.

# These are commonly set, remove the # and set as required.

Basedir = / usr/local/mysql

Datadir = / home/database

# port =.

# server_id =.

# socket =.

⑧ initializes the database and starts the database to change the random password

After the installation is complete, initialize the database

[root@DB-73 mysql] # rm-rf / home/database/*

[root@DB-73 mysql] # / usr/local/mysql/bin/mysqld-initialize-user=mysql-basedir=/usr/local/mysql-datadir=/home/database

2016-08-05T07:21:48.113959Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use-explicit_defaults_for_timestamp server option (see documentation for more details).

2016-08-05T07:21:48.114054Z 0 [Warning] 'NO_ZERO_DATE',' NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.

2016-08-05T07:21:48.114066Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.

2016-08-05T07:21:53.194657Z 0 [Warning] InnoDB: New log files created, LSN=45790

2016-08-05T07:21:54.195562Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2016-08-05T07:21:54.469438Z 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: 4941b58f-5add-11e6-8c4c-005056b36b6d.

2016-08-05T07:21:54.500735Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2016-08-05T07:21:54.502227Z 1 [Note] A temporary password is generated for root@localhost: aXx!

# / etc/init.d/mysql start

Starting MySQL (Percona Server).. [OK]

# mysql-uroot-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 2

Server version: 5.7.13-6

Copyright (c) 2009-2016 Percona LLC and/or its affiliates

Copyright (c) 2000, 2016, 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 > use mysql

Mysql > alter user 'root'@'localhost' identified by' jiayifei'

Query OK, 0 rows affected (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Mysql > quit

Add UDF to ⑨

# cat lib_mysqludf_sys-master/lib_mysqludf_sys.sql

Root@DB-73 lib_mysqludf_sys-master] # gcc-DMYSQL_DYNAMIC_PLUGIN-fPIC-Wall-I/usr/local/mysql/include-I. -shared lib_mysqludf_sys.c-o / usr/local/mysql/lib/mysql/plugin/lib_mysqludf_sys.so

[root@DB-73 lib_mysqludf_sys-master] # mysql-uroot-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 4

Server version: 5.7.13-6 Source distribution

Copyright (c) 2009-2016 Percona LLC and/or its affiliates

Copyright (c) 2000, 2016, 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 > DROP FUNCTION IF EXISTS lib_mysqludf_sys_info

Query OK, 0 rows affected, 1 warning (0.00 sec)

Mysql > DROP FUNCTION IF EXISTS sys_get

Query OK, 0 rows affected, 1 warning (0.00 sec)

.

⑩ # add Firewall

Port 3306 of the firewall is not enabled by default. To access it remotely, you need to open this port.

Open / etc/sysconfig/iptables

Under "- An INPUT-m state-- state NEW-m tcp-p-dport 22-j ACCEPT", add:

-An INPUT-m state-- state NEW-m tcp-p-dport 3306-j ACCEPT

Then save and close the file, and run the following command in the terminal to refresh the firewall configuration:

Firewalld is used as a firewall by default in service iptables restart CentOS 7, so after modifying iptables, it doesn't work after rebooting the system.

Add ports to Firewalld as follows:

Firewall-cmd-zone=public-add-port=3306/tcp-permanent

Firewall-cmd-reload

Thank you for reading this article carefully. I hope the article "what are the commands commonly used by linux in DB operation and maintenance" shared by the editor will be helpful to you. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.