MySQL Server Enterprise practice one

Introduction to the usage scenario of 1.MySQL Database

At present, the mainstream architecture of Web is LAMP (Linux+Apache+Mysql+PHP) and LNMP (Linux+Nginx+Mysql+PHP), and Mysql is favored by IT operation and maintenance staff and DBA.

The two major engines commonly used in MySQL are MyISAM and InnoDB, so what is the difference between them and how to choose them according to different situations.

Database tables of MyISAM type emphasize performance, which executes faster than InnoDB type, but does not provide transaction support and foreign keys. If you perform a large number of query operations, the MyISAM engine is a better choice.

InnoDB provides advanced database functions such as transaction support, foreign keys, and row-level locks, and performs a large number of insert or update operations. For performance reasons, you can use the InnoDB engine.

2.MySQL database installation mode

1) CentOS7.X installs MySQL based on YUM. Execute the command as follows:

# yum install mariadb-server mariadb mariadb-libs-y

2) method of installing MySQL 5.5.6 with source code

[root@localhost tools] # wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.62.tar.gz

[root@localhost tools] # tar-zxvf mysql-5.5.62.tar.gz

[root@localhost tools] # yum install cmake ncurses-devel ncurses-y

[root@localhost tools] # cd mysql-5.5.62

[root@localhost mysql-5.5.62] #

Cmake\

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql\

-DMYSQL_UNIX_ADDR=/tmp/mysql.sock\

-DMYSQL_DATADIR=/data/mysql\ # Database storage path

-DSYSCONFDIR=/etc\ # configuration file path

-DMYSQL_USER=mysql\ # running user

-DMYSQL_TCP_PORT=3306\

-DWITH_XTRADB_STORAGE_ENGINE=1\

-DWITH_MYISAM_STORAGE_ENGINE=1\ # enable MyISAM engine support

-DWITH_INNOBASE_STORAGE_ENGINE=1\ # enable InnoDB engine support

-DWITH_MEMORY_STORAGE_ENGINE=1\

-DWITH_READLINE=1\

-DENABLED_LOCAL_INFILE=1\

-DWITH_PARTITION_STORAGE_ENGINE=1\

-DWITH_EXTRA_CHARSETS=1\

-DEXTRA_CHARSETS=all\ # install and extend all character sets

-DDEFAULT_CHARSET=utf8\ # default character set

-DDEFAULT_COLLATION=utf8_general_ci\

-DWITH_BIG_TABLES=1\

-DWITH_DEBUG=0

# cmake reported the following error

-- Could NOT find Curses (missing: CURSES_LIBRARY CURSES_INCLUDE_PATH)

CMake Error at cmake/readline.cmake:83 (MESSAGE):

Curses library not found. Please install appropriate package

Remove CMakeCache.txt and rerun cmake.On Debian/Ubuntu, package name is libncurses5-dev, on Redhat and derivates it is ncurses-devel.

Call Stack (most recent call first):

Cmake/readline.cmake:118 (FIND_CURSES)

Cmake/readline.cmake:214 (MYSQL_USE_BUNDLED_READLINE)

CMakeLists.txt:394 (MYSQL_CHECK_READLINE)

[root@localhost mysql-5.5.62] # rm CMakeCache.txt # this step is very important

[root@localhost mysql-5.5.62] # yum install bison ncurses-devel git

# cmake # reconfigure the environment

[root@localhost mysql-5.5.62] # make & & make install

[root@localhost mysql] # pwd

/ usr/local/mysql

[root@localhost mysql] #\ cp support-files/my-large.cnf / etc/my.cnf

[root@localhost mysql] #\ cp support-files/mysql.server / etc/init.d/mysqld

[root@localhost mysql] # chkconfig-- add mysqld

[root@localhost mysql] # chkconfig-- level 35 mysqld on

[root@localhost mysql] # mkdir-p / data/mysql

[root@localhost mysql] # / usr/local/mysql/scripts/mysql_install_db-- user=mysql-- datadir=/data/mysql/-- basedir=/usr/local/mysql/ # initialize the database

Installing MySQL system tables...

[root@localhost mysql] # ln-s / usr/local/mysql/bin/ * / usr/bin/

[root@localhost mysql] # service mysqld restart

ERROR! MySQL server PID file could not be found!

Starting MySQL.Logging to'/ data/mysql/localhost.localdomain.err'.

. SUCCESS!

[root@localhost mysql] # mysql

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

Your MySQL connection id is 1

Server version: 5.5.62-log Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

MySQL [(none)] >

MySQL [(none)] > use mysql

Database changed

MySQL [mysql] > update user set password=password ('* *) where user='root'; # set password

Query OK, 4 rows affected (0.00 sec)

Rows matched: 4 Changed: 4 Warnings: 0

MySQL [mysql] > flush privileges

Detailed explanation of 3.MySQL database configuration file

[mysqld]

Port = 3306

Socket = / tmp/mysql.sock # Communication Settings

Skip-external-locking

Key_buffer_size = 256m # size of the index buffer

Max_allowed_packet = 1m

Table_open_cache = 256 # number of caches for open tables

Sort_buffer_size = 1m

Read_buffer_size = 1m # buffer size that can be used by read query operations

Read_rnd_buffer_size = 4m

Myisam_sort_buffer_size = 64m

Thread_cache_size = 8 # number of reusable threads

Query_cache_size= 16m # query result buffer size

# Try number of CPU's*2 for thread_concurrency

Thread_concurrency = 8 # maximum number of threads, server logic CPU*2