Shulou(Shulou.com)06/01 Report--

1.1 introduction to mysql

Mysql is a traditional relational database product, its open architecture makes users highly selective, at the same time, there are many community developers and maintenance personnel, its function is stable, the performance is excellent, and under the premise of complying with the GPL protocol, it can be used and modified free of charge, which also brings more convenience for the promotion and use of mysql. In the process of mysql growth, the support for the platform is getting better and better. Mysql is a relational database management system. The characteristic of relational database is to save the data in different tables, and then put these tables into different data, instead of putting all the data in a large warehouse. This design increases the reading speed of mysql, and the flexibility and management have also been greatly improved. Accessing and managing mysql database is a commonly used standardized language for sql structured query language.

1.2 introduction to the background of the birth of MariaDB database

Since Oracle acquired MYSQL, its market share in the field of commercial database and open source database has jumped to the first place. This pattern has caused many people in the industry to worry, because the boss of commercial database will close the source of mysql, in order to prevent oracle from closing the source, while open source mysql database is available, the mysql community uses branching to avoid this risk. MariaDB database was born, and mariadb is a backward compatible. May replace mysql products in the future, the official website address is https://mariadb.org/, however, it is recommended that you choose to be more stable, use a more extensive mysql database, you can test the mariadb database first, and so on, after more users, the community is more active and consider using it.

1.3 what is mysql multi-instance

To put it simply, mysql multi-instance is to open multiple different service ports (such as 3306 and 3307) on a server and run multiple mysql service processes at the same time. These service processes provide services through different socket listening on different server ports. These mysql multi-instances share a set of mysql installers, using different my.cnf (or the same) configuration files, launchers (or the same) and data files. When providing services, multi-instance mysql appears to be logically independent, and they obtain the corresponding amount of hardware resources of the server according to the corresponding settings of multiple configuration files.

1.4 the functions and problems of mysql multiple instances

Effective use of server resources

When a single server resource is left, it can make full use of the remaining resources to provide more services, and the logical isolation of resources can be realized.

Save server resources

When the company is short of funds, but the database needs to provide services independently as far as possible, and when technologies such as master-slave replication are needed, multiple instances are better.

Mysql multi-instance has its advantages, but it also has disadvantages, for example, there is the problem of resource preemption.

When the concurrency of a database instance is very high or there is a sql slow query, the whole instance will consume a lot of resources such as system cpu, disk and so on, resulting in a decline in the quality of service provided by other database instances on the server, which is equivalent to different bedrooms in the house where everyone lives. When they get up to go to work in the morning, they have to brush their teeth, wash their faces, etc., so that the toilet will be occupied for a long time, and others have to wait for the same. The resources obtained by different instances are relatively independent and cannot be completely isolated like virtualization.

1.5 RDBMS and NOSQL comparison

1.5.1 characteristics of Relational Database

Typical products of two-dimensional table

Oracle traditional enterprise, mysql is an Internet enterprise

Data is stored through SQL

The biggest feature: strong data security (ACID)

Web1.0 era

Enterprises provide content and users browse, so the relational database is sufficient and the concurrency is not high, so there is no need for nosql

Web2.0 era

The core is that the enterprise provides the platform, and the user participates in providing the content, so the relational database can not meet the demand.

2003 NOSQL

With the emergence of memcached, the point of the relationship is performance, but the security concern is relatively low. With the continuous improvement of security requirements, there is redis.

1.5.2 characteristics of redis

Still high performance the concurrent data persistence function supports multiple data types, master-slave replication and cluster management no longer use sql

1.6 mysql installation environment preparation

Download 5.6.36

Installation package to view the current linux system environment

[root@web01 ~] # cat / etc/redhat-release CentOS release 6.9 (Final) [root@web01 ~] # getenforce Disabled [root@web01 ~] # / etc/init.d/iptables statusiptables: Firewall is not running.

Install dependency package `[root@web02 ~] # yum install ncurses-devel libaio-devel-y`br / >` [root@web02 ~] # yum install ncurses-devel libaio-devel-y`

Install cmake (compile and install) `yum install cmake-y`

* * set users * *

`[ root@web02 ~] # useradd-s / sbin/nologin-M mysql`br / > `yum install cmake-y`

* * set users * *

`[ root@web02 ~] # useradd-s / sbin/nologin-M mysql`

Decompression and installation

[root@web02 ~] # cd / server/tools/ [root@web02 tools] # tar xf mysql-5.6.36.tar.gz [root@web02 tools] # cd mysql-5.6.36 [root@web02 mysql-5.6.36] # cmake. -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.36\ >-DMYSQL_DATADIR=/application/mysql-5.6.36/data\ >-DMYSQL_UNIX_ADDR=/application/mysql-5.6.36/tmp/mysql.sock\ >-DDEFAULT_CHARSET=utf8\ >-DDEFAULT_COLLATION=utf8_general_ci\ >-DWITH_EXTRA_CHARSETS=all\ >-DWITH_INNOBASE_STORAGE_ENGINE=1\ >-DWITH_FEDERATED_STORAGE_ENGINE=1\ >-DWITH_BLACKHOLE_STORAGE_ENGINE=1\ >-DWITHOUT_EXAMPLE_STORAGE _ ENGINE=1\ >-DWITH_ZLIB=bundled\ >-DWITH_SSL=bundled\ >-DENABLED_LOCAL_INFILE=1\ >-DWITH_EMBEDDED_SERVER=1\ >-DENABLE_DOWNLOADS=1\ >-DWITH_DEBUG=0

Compilation and installation

Make & & make install

Create a soft connection

Ln-s / application/mysql-5.6.36/ / application/mysql

Initialize and configure the database

[root@web02 ~] # cd / application/mysql [root@web02 mysql] # cp support-files/my*.cnf / etc/my.cnf

Initialize configuration `[root@web02 mysql] # / application/mysql/scripts/mysql_install_db-- basedir=/application/mysql/-- datadir=/application/mysql/data-- user= mysql`br / >` [root@web02 mysql] # / application/mysql/scripts/mysql_install_db-- basedir=/application/mysql/-- datadir=/application/mysql/data-- user= mysql`

Start the error report

[root@web02 ~] # / etc/init.d/mysqld restart RROR! MySQL server PID file could not be started MySQL.Logging to'/ application/mysql-5.6.36/data/web02.err'.171114 21:41:56 mysqld_safe Directory'/ application/mysql-5.6.36/tmp' for UNIX socket file don't exists. ERROR! The server quit without updating PID file (/ application/mysql-5.6.36/data//web02.pid).

Remedial method

[root@web02 ~] # cd / application/mysql [root@web02 mysql] # touch tmp [root@web02 mysql] # chown-R mysql.mysql tmp

User authorization `[root@web02 mysql] # chown-R mysql.mysql / application/mysql/ `br / >` [root@web02 mysql] # chown-R mysql.mysql / application/mysql/ `

Authorize and join boot self-startup

Cp support-files/mysql.server / etc/init.d/mysqldchmod 700 / etc/init.d/mysqldchkconfig mysqld onchkconfig-- list mysqld/etc/init.d/mysqld startnetstat-lntup | grep 3306

Crack the mysql user password and start it in the background.

Mysqld_safe-defaults-file=/application/mysql/my.cnf-skip-grant-tables &

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.