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

Mysql tar source code edit installation operation

The following operating system is CentOS 7.2 and the MySQL version is mysql-5.7.17.

1. Install according to the package before installation

Yum-y install gcc gcc-c++ ncurses ncurses-devel cmake bison bison-devel openssl openssl-devel pcre pcre-devel

two。 Download the source package

2.1it is recommended to download http://dev.mysql.com/downloads/mysql/ from the official website and choose "Source Code". In the process of downloading, you need to sign up for an Oracle account. If you have a direct login, it is good.

2.2 provide a download address: http://101.96.10.47/dev.mysql.com/get/Downloads/MySQL-5.7/mysql-boost-5.7.17.tar.gz

Or https://sourceforge.net/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz.

The boost library has been added to the new version of the source code. If you do not need to download this library in the source code, otherwise an error will be reported, so you need to click below.

3. New MySQL users and user groups

Groupadd-r mysql

Useradd-r-g mysql mysql

4. Enter the installation step

Tar-zvxf mysql-boost-5.7.17.tar.gz

Cd mysql-5.7.17

Create a new MySQL database data file directory

Mkdir-p / www/mysql-data

Mkdir-p / www/mysql-logs

Mkdir-p / www/mysql-temp

Make an edit

Cmake-DCMAKE_INSTALL_PREFIX=/www/mysql\

-DMYSQL_DATADIR=/www/mysql-data\

-DMYSQL_UNIX_ADDR=/www/mysql/mysql.sock\

-DWITH_BOOST=/www/boost_1_59_0\

-DSYSCONFDIR=/etc\

-DWITH_INNOBASE_STORAGE_ENGINE=1\

-DWITH_PARTITION_STORAGE_ENGINE=1\

-DWITH_FEDERATED_STORAGE_ENGINE=1\

-DWITH_BLACKHOLE_STORAGE_ENGINE=1\

-DWITH_MYISAM_STORAGE_ENGINE=1\

-DENABLED_LOCAL_INFILE=1\

-DENABLE_DTRACE=0\

-DDEFAULT_CHARSET=utf8mb4\

-DDEFAULT_COLLATION=utf8mb4_general_ci\

-DWITH_EMBEDDED_SERVER=1

The above configuration explains:

-DCMAKE_INSTALL_PREFIX=/www/mysql / / set the installation directory

-DMYSQL_DATADIR=/www/mysql/data / / set the database storage directory

-DMYSQL_UNIX_ADDR=/www/mysql/mysql.sock / / set the UNIX socket directory

-DDEFAULT_CHARSET=utf8mb4 / / sets the default character set

-DDEFAULT_COLLATION=utf8mb4_general_ci / / set default proofreading rules

-DWITH_INNOBASE_STORAGE_ENGINE=1 / / add InnoDB engine support

-DSYSCONFDIR=/etc / / sets the directory where the my.cnf configuration file is located. It defaults to the installation directory. If necessary, you can add one later.

For editing and installation

Make & & make install

# clean up the 00:00 file after the installation is complete

Make clean

Set the startup script to boot (that is, make mysql as a service)

# copy the executable file to the specified directory and change the name to mysqld

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

Cp / www/mysql/bin/mysqld / usr/bin/mysqld

# Grant executable permissions

Chmod + x / etc/init.d/mysqld

# set to boot

Systemctl enable mysqld

Modify the executable directory of mysql

Chown-Rf mysql:mysql / www/mysql

Chown-Rf mysql:mysql / www/mysql-data

Chown-Rf mysql:mysql / www/mysql-logs

Chown-Rf mysql:mysql / www/mysql-temp

The configuration file modifies / etc/my.cnf

# reference. For more information on the parameters, please search by yourself.

[mysqld]

Character-set-server = utf8mb4

Collation-server = utf8mb4_general_ci

Skip-external-locking

Skip-name-resolve

User = mysql

Port = 3306

Basedir = / www/mysql

Datadir = / www/mysql-data

Tmpdir = / www/mysql-temp

# server_id =.

Socket = / www/mysql/mysql.sock

Log-error = / www/mysql-logs/mysql_error.log

Pid-file = / www/mysql-data/mysql.pid

Open_files_limit = 10240

Back_log = 600,

Max_connections=500

Max_connect_errors = 6000

Wait_timeout=605800

# open_tables = 600

# table_cache = 650

# opened_tables = 630

Max_allowed_packet = 32m

Sort_buffer_size = 4m

Join_buffer_size = 4m

Thread_cache_size = 300

Query_cache_type = 1

Query_cache_size = 256m

Query_cache_limit = 2m

Query_cache_min_res_unit = 16k

Tmp_table_size = 256m

Max_heap_table_size = 256m

Key_buffer_size = 256m

Read_buffer_size = 1m

Read_rnd_buffer_size = 16m

Bulk_insert_buffer_size = 64m

Lower_case_table_names=1 (case-sensitive settings)

Default-storage-engine = INNODB

Innodb_buffer_pool_size = 1G

Innodb_log_buffer_size = 32m

Innodb_log_file_size = 128m

Innodb_flush_method = O_DIRECT

#

Long_query_time= 2

Slow-query-log = on

Slow-query-log-file = / www/mysql-logs/mysql-slow.log

[mysqldump]

Quick

Max_allowed_packet = 32m

Character_set_server = utf8

[mysqld_safe]

Log-error=/var/log/mysqld.log

Pid-file=/var/run/mysqld/mysqld.pid

Character_set_server = utf8

Password: the above character_set_server = utf8 is used to solve the number problem.

Add environment variable vi / etc/profile

# add the following at the end

# mysql env

Export PATH=$PATH:/www/mysql/bin:/www/mysql/lib

Make the configuration file effective source / etc/profile

Initialize the database

Mysqld-initialize-insecure-user=mysql-basedir=/www/mysql-datadir=/www/mysql-data

Note:

Previous versions of MySQL, mysql_install_db, are under mysql_basedir/script.

MySQL 5.7is placed directly under the mysql_install_db/bin directory.

"- initialize" has been discarded, generate a random password (~ / .mysql_secret)

"- initialize-insecure" does not generate a password

There can be no data files in the "- datadir" directory

Or use the following sentences:

Mysql_install_db-user=mysql-basedir=/www/mysql-datadir=/www/mysql-data

Start the database

Systemctl start mysqld

View database status

Systemctl status mysqld

View mysql service processes and ports

Ps-ef | grep mysql

Netstat-tunpl | grep 3306

Set the database root user password

MySQL is the same as the Oracle database, which comes with a root user by default (this is the same as the

Root users are completely irrelevant), we initialize the password of the root user after setting up the security configuration of the MySQL database.

During the preparation process, just type y all the way. This only shows that in the MySQL5.7.17 version, the user's password policy is divided into low-level ones.

There are three kinds of LOW, medium MEDIUM and super STRONG. Medium MEDIUM level is recommended! Of course, you don't have to wait.

Level setting

After consulting the official documents, it is found that there are three password policies:

Policy test implementation

0-LOW length

1-MEDIUM length; numbers, lowercase / uppercase and special characters

2-STRONG length; numbers, lowercase / uppercase and special characters; dictionary files

License: future usage related secrets will need to be implemented in accordance with this standard, but relevant modifications can also be made.

Mysql_secure_installation

Except

Disallow root login remotely

Remove test database and accesss to it can be n

Everything else is y.

Enter the database

Mysql-uroot-p password

"build other uses"

Mysql > create user 'hua'@'localhost' identified by' hlj123'; (can be modified later)

Set up the storage engine

Mysql > set storage_engine=INNODB

Build database

Mysql > create database zgz character set utf8

Check to see if the establishment is successful

Mysql > show databases

Permission to use related database permissions

Mysql > GRANT ALL PRIVILEGES ON *. * TO 'root'@'10.134.100.174' IDENTIFIED BY' FLM@88' WITH GRANT OPTION

Mysql > flush privileges

Check whether the database permission is enabled for the number of connections.

Mysql-h 10.134.100.174-uroot-pFLM@88 zgz

If the database entry is successful, otherwise restart the mysql service and try again

If the server is allowed to access the related database through any server

Mysql > GRANT ALL PRIVILEGES ON databasename.tables TO 'root'@'%' IDENTIFIED BY' Foxconn@88' WITH GRANT OPTION

Mysql > flush privileges

Find out the number of databases you need

Mysqldump-h 10.157.136.134-uroot-proot-- events-- ignore-table=mysql.event-- default-character-set=UTF8 jspxcms > zgz.sql

Create an empty number in advance to store the data that needs to be imported, and then you only need to execute the database boot command

Mysql > use zgz

Mysql > source / root/zgz.sql (this is where you put the zgz.sql)

Determine whether the data table is created successfully, that is, whether the data file is imported successfully

Mysql > show tables

When you need to check the pre-password policy, you can do the following:

Mysql > show VARIABLES like "password%"

+-+ +

| | Variable_name | Value |

|-- +-|

| | default_password_lifetime | 0 | |

| | disconnect_on_expired_password | ON |

| | log_builtin_as_identified_by_password | OFF |

| | mysql_native_password_proxy_users | OFF |

| | old_passwords | 0 | |

| | report_password |

| | sha256_password_proxy_users | OFF |

| | validate_password_dictionary_file |

| | validate_password_length | 8 |

| | validate_password_mixed_case_count | 1 | |

| | validate_password_number_count | 1 | |

| | validate_password_policy | MEDIUM |

| | validate_password_special_char_count | 1 | |

+-+ +

13 rows in set

Time: 0.030s

Modify the default password policy (of course, it is not recommended to change it to a lower security policy in the actual environment)

Mysql > set global validate_password_policy = 0

Validate_password_number_count specifies the length of the data in the password

Validate_password_special_char_count specifies the length of special characters in the password

Validate_password_mixed_case_count specifies the length of uppercase and lowercase letters in the password.

The default value of these parameters is 1, so the minimum value of validate_password_length is 4.

If you show that the value of the specified validate_password_length is less than 4, although it will not report an error

However, the value of validate_password_length will be set to 4.

Modify validate_password_number_count,validate_password_special_char_count

Any one of the values in validate_password_mixed_case_count, validate_password_length will be modified dynamically

In addition, the level of password policy can be set through the my.cnf profile

Vi / etc/my.cnf

[mysqld]

Validate_password_policy=2

When validate_password_policy sets mysql to start, if the password policy level is set to 3, then you need to specify a dictionary file

Of course, you can also close the validate_password plug-in through the my.cnf configuration file.

Just add a row

Validate_password = off

After editing the configuration file, restart the mysqld service to take effect.

Mysql > show VARIABLES like "validate_password%"

+-+ +

| | Variable_name | Value |

| |-+-|

+-+ +

0 rows in set

Time: 0.008s

After you close the validate_password plug-in, you don't have some parameter variables for validate_password.

The new version of MySQL listens by default on the address family of IPv6. Change to listen on IPv4 address family

Modify my.cnf to add one line configuration: bind-address = 0.0.0.0

Just restart mysqld.

Mysql master master replication configuration

First of all, install the above steps for installing mysql on the server to ensure that the server is available.

Therefore, database server environment:

+-

| Server node 1 | Server node 2 | |

+-+-+

| | OS | CentOS 7.2 | CentOS 7.2 | |

+-+-+

| | DB Version | MySQL 5.7.17 | MySQL 5.7.17 | |

+-+-+

| | HostName | Linux01 | Linux02 | |

+-+-+

| | IPADDR | 172.16.100.69 | 172.16.100.70 | |

+-+-+

Users needed to create replication. Users need slave permissions (necessary) and file,select permissions (optional).

Mysql > grant replication slave, file, select on *. * to 'mysql_sync'@'172.16.100.69' identified by' FLM88'

Mysql > grant replication slave, file, select on *. * to 'mysql_sync'@'172.16.100.70' identified by' FLM88'

Note: if you want to have permission to execute "LOAD TABLE FROM MASTER" on Slave or

If you use the "LOAD DATA FROM MASTER" statement, you must grant global FILE and SELECT permissions.

The above sentence is to set the synchronization database usage mysql_sync, password and related permissions.

Modify the configuration files vi / etc/my.cnf on the two servers respectively

My.cnf on 172.16.100.69:

Log-bin=mysql-bin-db01

Server-id=11

Log-slave-updates

Slave-skip-errors=all

Auto_increment_increment=2

Auto_increment_offset=1

My.cnf on 172.16.100.70:

Log-bin=mysql-bin-db02

Server-id=12

Log-slave-updates

Slave-skip-errors=all

Auto_increment_increment=2

Auto_increment_offset=2

Server-id servers: the two servers must be configured differently

Auto_increment_increment configures the step size of each increase in the automatic growth field in each node

How much does auto_increment_offset configure the initial value of each node auto-growth field to start with?

If we do not set these two variables, tables with auto-growing fields will be maintained on multiple servers, and duplication will lead to replication errors.

As set above, the value generated by the self-growing field of node A will be 1pm 3pm 5.

And the B node will be 2, 4, 4, 6.

Restart two database instances

Systemctl restart mysqld

Execute in two databases: show master status; to view the status of the primary server:

172.16.100.69 (peak 1):

Mysql > show master status

+-- +

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-- +

| | mysql-bin-db01.000001 | 1096 | |

+-- +

1 row in set (3.71 sec)

172.16.100.70 (peak 2):

Mysql > show master status

+-- +

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-- +

| | mysql-bin-db02.000001 | 625 |

+-- +

1 row in set (0.00 sec)

Configure slave server Slave (executed separately by two nodes)

Point 1:

Mysql > change master to master_host='172.16.100.70',master_user='mysql_sync'

Master_password='Foxconn88',master_log_file='mysql-bin-db02.000001',master_log_pos=625

Mysql > start slave; / / start the copy from server function

Point 2:

Mysql > change master to master_host='172.16.100.69',master_user='mysql_sync'

Master_password='Foxconn88',master_log_file='mysql-bin-db01.000001',master_log_pos=1096

Mysql > start slave; / / start the copy from server function

Check status:

Mysql > show slave status\ G

No problem. Master related to each other can be found in their respective databases.

Because it is installed by the source code, you need to add mysqldump to the service. You can simply add it to the service.

Ln / www/mysql/bin/mysqldump / usr/bin/mysqldump

Set the number of copies of the database and use the crontab execution script to do so.

#! / usr/bin/env bash

# a

# Author: Zhang Huangbin (zhb@iredmail.org)

# Date: 16/09/2007

KEEP_DAYS='90'

Export BACKUP_ROOTDIR= "/ data0/db_bak/"

Export MYSQL_USER= "root"

Export MYSQL_PASSWD= "FLM@88"

Export DATABASES= "zgz"

Export DB_CHARACTER_SET= "utf8"

Export PATH='/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/usr/local/sbin'

Export CMD_DATE='/bin/date'

Export CMD_DU='du-sh'

Export CMD_COMPRESS='bzip2-9'

Export COMPRESS_SUFFIX='bz2'

Export CMD_MYSQLDUMP='mysqldump'

Export CMD_MYSQL='mysql'

Export YEAR= "$(${CMD_DATE} +% Y)"

Export MONTH= "$(${CMD_DATE} + m)"

Export DAY= "$(${CMD_DATE} + d)"

Export TIME= "$(${CMD_DATE} +% H:%M:%S)"

Export TIMESTAMP= "${YEAR}-${MONTH}-${DAY}-${TIME}"

Export BACKUP_SUCCESS='YES'

Export BACKUP_DIR= "${BACKUP_ROOTDIR} / mysql/$ {YEAR} / ${MONTH} / ${DAY}"

Mkdir-p $BACKUP_ROOTDIR

Mysqldump-h 10.134.100.174-uroot-pFLM@88

-events-- ignore-table=mysql.event-- default-character-set=UTF8 zgz > $BACKUP_ROOTDIR$TIMESTAMP.sql

The above is just a corresponding example and can be used as soon as possible.

Because I have been doing this MySQL cluster for a long time, there may be some mistakes in writing now, if you find it, welcome to point it out, thank you!

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.