linux Complete steps for installing mysql 8.0. x

MySQL

Introduction to MySQL

MySQL was originally an open source relational database management system. The original developer was MySQL AB of Sweden, which was acquired by Shengyang Microsystems (Sun Microsystems) in 2008. In 2009, Oracle Corporation (Oracle) acquired Shengyang Microsystems Company, and MySQL became a product of Oracle.

In the past, MySQL has become the most popular open source database because of its high performance, low cost and good reliability, so it is widely used in small and medium-sized websites on Internet. As MySQL matures, it is also being used in more large-scale websites and applications, such as Wikipedia, Google and Facebook. "M" in the popular open source software portfolio LAMP refers to MySQL.

However, after being acquired by Oracle, Oracle greatly increased the price of MySQL commercial version, and Oracle no longer supported the development of another free software project OpenSolaris, which caused the free software community to have hidden concerns about whether Oracle will continue to support MySQL community version (the only free version among MySQL). Michael Widnius, founder of MySQL, set up a branch plan MariaDB based on MySQL. However, some open source software that used MySQL gradually turned to MariaDB or other databases. For example, Wikipedia officially announced in 2013 that it would migrate from MySQL to MariaDB database [6].

Relational database

Relational database (English: Relational database) is a database based on relational model, which deals with the data in the database with the help of mathematical concepts and methods such as set algebra. All kinds of entities in the real world and all kinds of connections between entities are represented by relational models. The relational model was first proposed by Edgar Cod in 1970, and was combined with Cod's 102 Law. Although there are some criticisms about this model nowadays, it is still the traditional standard of data storage. Standard data query language SQL is a language based on relational database, which performs the retrieval and operation of data in relational database.

The relational model consists of three parts: relational data structure, relational operation set and relational integrity constraint.

MySQL characteristics

MySQL is a widely used database with the following features:

Written with C and C + +, and tested with a variety of compilers to ensure the portability of the source code Support AIX, FreeBSD, HP-UX, Linux, Mac OS, Novell Netware, OpenBSD, OS/2 Wrap, Solaris, Windows and other operating systems.    API is provided for many programming languages. Programming languages include C, C + +, Python, Java, Perl, PHP, Eiffel, Ruby and Tcl.    Support multithreading and make full use of CPU resources The optimized SQL query algorithm can effectively improve the query speed It can be used as a separate application in the client-server network environment or as a library Embedded in other software to provide multi-language support, common codes such as Chinese GB 2312, BIG5, Japanese Shift_JIS can be used as data table names and data column names Provide various database connection channels such as TCP/IP, ODBC and JDBC Provide management tools for managing, checking and optimizing database operations Can handle large databases with tens of millions of records

MySQL application

Compared with large databases such as Oracle, DB2, SQL Server, MySQL has its own shortcomings, such as small scale and limited functions (MySQL Cluster is relatively poor in function and efficiency), but this does not reduce its popularity at all. MySQL provides more than enough functionality for ordinary individual users and small and medium-sized enterprises, and because MySQL is open source software, it can greatly reduce the total cost of ownership. At present, the popular website architecture on Internet is LAMP (Linux+Apache+MySQL+PHP), that is, Linux is used as operating system, Apache is used as Web server, MySQL is used as database and PHP is used as server script interpreter. Since Linux+Apache+MySQL+PHP are both free or open source software (FLOSS), you can build a stable, free web system using LAMP at no cost of a penny.

MySLQ Storage Engine

Introduction to MySQL Storage Engine

Plug-in storage engine is one of the most important features of MySQL database. Users can choose how to store and index the database and whether to use transactions according to the needs of applications. mySQL supports a variety of storage engines by default to meet the needs of database applications in different fields. Users can choose to use different storage engines to improve the efficiency of applications and provide flexible storage. Users can customize and use their own storage engines according to their own needs to achieve maximum customization.

The commonly used storage engines of MySQL are MyISAM, InnoDB, MEMORY and MERGE, among which InnoDB provides transaction safe tables, while other storage engines are non-transaction safe tables.

MyISAM is the default storage engine for MySQL. MyISAM does not support transactions or foreign keys, but it is fast to access and requires no transaction integrity.

The InnoDB storage engine provides transaction security with commit, rollback, and crash recovery capabilities. However, compared with the MyISAM storage engine, InnoDB writes are less efficient and take up more disk space to retain data and indexes. MySQL supports foreign key storage engine only InnoDB. When creating foreign keys, it is required that schedules must have corresponding indexes, and sub-tables will automatically create corresponding indexes when creating foreign keys.

MySQL Storage Engine Features

It is mainly embodied in performance, transaction, concurrency control, referential integrity, cache, failure recovery, backup and memory-back and so on

At present, the popular storage engines are MyISAM and InnoDB, and MyISAM is the first choice for most Web applications. The main differences between MyISAM and InnoDB are in performance and transaction control.

MyISAM is an extended implementation of the early ISAM (Indexed Sequential Access Method). ISAM is designed to handle the situation that the read frequency is much higher than the write frequency. Therefore, ISAM and later MyISAM do not consider the support for transactions, do not need transaction records, and the query efficiency of ISAM is considerable, and the memory consumption is very small.

While inheriting the advantages of ISAM, MyISAM provides a large number of practical new features and related tools with the times. For example, considering concurrency control, table-level locks are provided.

Moreover, because MyISAM uses independent storage files for each table (MYD data file and MYI index file), it is convenient to backup and restore 10 points (copy coverage is enough), and it also supports online recovery.

Therefore, if the application does not need transactions, does not support foreign keys, and only handles basic CRUD (add, delete, modify and check) operations, then MyISAM is no 2 choice.

Install mysql8.0 under linux (CentOS7.5_x86_64)

#  Download mysql 

$ wget http://mirrors.163.com/mysql/Downloads/MySQL-8.0/mysql-8.0.13-el7-x86_64.tar.gz

#  Decompression 
$ mysql tar -zxvf mysql-8.0.4-rc-linux-glibc2.12-x86_64.tar.gz -C /usr/local

#  Modify folder name  
$ mv mysql-8.0.4-rc-linux-glibc2.12-x86_64/ mysql

 Add a default profile 
$ vim/etc/my.cnf

[client]
port=3306
socket=/tmp/mysql.sock

[mysqld]
port=3306
user=mysql
socket=/tmp/mysql.sock
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data


#  Create mysql Group 
$ groupadd mysql

#  Create mysql Users 
$ useradd -g mysql mysql

#  Create mysql Data catalog 
$ mkdir $MYSQL_HOME/data

#  Initialization mysql 
$ /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/


#  Initialization error report 
bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

#  Solution 
yum install -y libaio

#  Initialization error report 
2018-07-08T02:53:24.542370Z 0 [System] [MY-010116] /usr/local/mysql/bin/mysqld (mysqld 8.0.4-rc) starting as process 17745 ...
mysqld: Can't create/write to file '/tmp/mysql/data/ibd35qXQ' (Errcode: 13 - Permission denied)
2018-07-08T02:53:24.554816Z 1 [ERROR] [MY-011066] InnoDB: Unable to create temporary file; errno: 13
2018-07-08T02:53:24.554856Z 1 [ERROR] [MY-011066] InnoDB: InnoDB Database creation was aborted with error Generic error. You may need to delete the ibdata1 file before trying to start up again.
2018-07-08T02:53:24.555000Z 0 [ERROR] [MY-010020] Data Dictionary initialization failed.
2018-07-08T02:53:24.555033Z 0 [ERROR] [MY-010119] Aborting
2018-07-08T02:53:24.555919Z 0 [System] [MY-010910] /usr/local/mysql/bin/mysqld: Shutdown complete.

#  Solution: Modify /tmp/mysql Directory permissions of 
$ chown -R mysql:mysql /tmp/mysql


#  Initialization succeeded 
>  If there is no exception, the log can be seen as follows mysql Will be generated by default root Account number and password root@localhost: /TI(mjVAs1Ta

[root@localhost mysql]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
2019-01-29T10:19:34.023997Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.13) initializing of server in progress as process 4240
2019-01-29T10:19:39.764895Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: /TI(mjVAs1Ta
2019-01-29T10:19:43.041419Z 0 [System] [MY-013170] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.13) initializing of server has completed

#  Copy mysql Start the file to the system initialization directory 
$ cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld


#  Start mysql Server 
$ service mysqld start

mysql Basic Operations

#  Use mysql Client connection mysql
$ /usr/local/mysql/bin/mysql -u root -p password

 Modify mysql Default initialization password for 
> alter user 'root'@'localhost' identified by 'root';

#  Create a user  CREATE USER ' User name '@' Host name ' INDENTIFIED BY ' User password '
> create user 'jack'@'localhost' identified by 'jack';

#  Grant permission  grant  Authority  on  Database . Table  to ' User name '@' Login host ' [INDENTIFIED BY ' User password '];
> grant replication slave on *.* to 'jack'@'localhost';

#  Refresh 
# $ flush privileges; 

#  Modify root Users can connect remotely 
> update mysql.user set host='%' where user='root';

#  View mysql Users used 
> select user,host from mysql.user;

# docker  Modify mysql Maximum number of connections of 
apt-get update
apt-get install vim
vim /etc/mysql/mysql.conf.d/mysqld.cnf
max_connections=1000

> alter user 'root'@'%' identified with mysql_native_password by 'root';

mysql Cluster Master-Slave Replication

Prepare two installed mysql servers

#  Configure the main service to add the following configuration 
$ vim /etc/my.cnf

#  Node only 1id Value 
server-id=1

#  Open 2 Binary log 
log-bin=mysql-bin

#  Specify the log format   Have mixed|row|statement  Recommend mixed
binlog-format=mixed

#  Step value auto_imcrement . 1 Have like n Taiwan master MySQL Just fill it out n( Optional configuration )
auto_increment_increment=2 

#  Starting value. 1 Fill in the first place n Taiwan master MySQL . At this time, it is the first 1 Taiwan master MySQL( Optional configuration )
auto_increment_offset=1 

#  Ignore mysql Library ( Optional configuration )
binlog-ignore=mysql 

#  Ignore information_schema Library ( Optional configuration )
binlog-ignore=information_schema 

#  The database to synchronize, all libraries by default ( Optional configuration )
replicate-do-db=db1


# slave  Node configuration 

#  Node only 1id Value 
server-id=2

#  Open 2 Binary log 
log-bin=mysql-bin

#  Step value auto_imcrement . 1 Have like n Taiwan master MySQL Just fill it out n( Optional configuration )
auto_increment_increment=2

#  Starting value. 1 Fill in the first place n Taiwan master MySQL . At this time, it is the first 1 Taiwan master MySQL( Optional configuration )
auto_increment_offset=2

#  The database to synchronize, all libraries by default ( Optional configuration )
replicate-do-db=db1


#  View  master  State of  ,  Especially the current log and location 
> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |  1608 |    |     |     |
+------------------+----------+--------------+------------------+-------------------+

#  In slave Node executes the following command 

 Attention master_log_file  Is corresponding to show master status; Medium file The value of, master_log_pos Is corresponding to position Value of 

> change master to
master_host='192.168.79.15',
master_user='root',
master_password='root',
master_log_file='mysql-bin.000009',
master_log_pos=0;

#  Start  slave  Status  (  Start listening  msater  Change of  )
> start slave;

#  View  slave  State of  
> show slave status\G

*************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
     Master_Host: 192.168.79.15
     Master_User: root
     Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000009
   Read_Master_Log_Pos: 863
    Relay_Log_File: node-6-relay-bin.000002
    Relay_Log_Pos: 500
  Relay_Master_Log_File: mysql-bin.000009
    Slave_IO_Running: Yes
   Slave_SQL_Running: Yes
    Replicate_Do_DB: 
   Replicate_Ignore_DB: 
   Replicate_Do_Table: 
  Replicate_Ignore_Table: 
  Replicate_Wild_Do_Table: 
 Replicate_Wild_Ignore_Table: 
     Last_Errno: 0
     Last_Error: 
     Skip_Counter: 0
   Exec_Master_Log_Pos: 863
    Relay_Log_Space: 709
    Until_Condition: None
    Until_Log_File: 
    Until_Log_Pos: 0
   Master_SSL_Allowed: No
   Master_SSL_CA_File: 
   Master_SSL_CA_Path: 
    Master_SSL_Cert: 
   Master_SSL_Cipher: 
    Master_SSL_Key: 
  Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
    Last_IO_Errno: 0
    Last_IO_Error: 
    Last_SQL_Errno: 0
    Last_SQL_Error: 
 Replicate_Ignore_Server_Ids: 
    Master_Server_Id: 1
     Master_UUID: 6291c709-23af-11e9-99fb-000c29071862
    Master_Info_File: mysql.slave_master_info
     SQL_Delay: 0
   SQL_Remaining_Delay: NULL
  Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
   Master_Retry_Count: 86400
     Master_Bind: 
  Last_IO_Error_Timestamp: 
  Last_SQL_Error_Timestamp: 
    Master_SSL_Crl: 
   Master_SSL_Crlpath: 
   Retrieved_Gtid_Set: 
   Executed_Gtid_Set: 
    Auto_Position: 0
   Replicate_Rewrite_DB: 
     Channel_Name: 
   Master_TLS_Version: 
  Master_public_key_path: 
  Get_master_public_key: 0

#  When Slave_IO_Running: Yes And Slave_SQL_Running: Yes Dall for yes It means that master-slave replication is normal 


# Reset  slave  Status  .
$ reset slave;

# Suspend  slave  Status  ;
$ stop slave;

Summarize