Binary installation MySQL Database

Today installed is the binary MySQL package 5.7.21 package, in the configuration file when a lot of pits, left or done, to share with you

Binary msyql5.7.21 version master-slave copy installation

New/picclife Directory

Mkdir/picclife

New/picclife/data Directory

Mkdir/picclife/data

Create users and groups for MySQL

Groupadd MySQL

useradd-g MySQL MySQL

Upload the binary MySQL package to the/picclife directory

Unpacking Packages

TAR-ZXF Binary Package

Renamed

MV extract out of the package MySQL

belong to the group owner

Chown-r Mysql:mysql MySQL

Configuring Environment variables

Vim/etc/profile

Mysql_home=/picclife/mysql

Export path= $PATH: $mysql _home/bin

Effect

Source/etc/profile

Editing a configuration file

Vim/etc/my.cnf

[Client]

Port = 3306

Socket =/tmp/mysql.sock

# #default-character-set = UTF8

[MySQL]

Port = 3306

Socket =/tmp/mysql.sock

#default-character-set = UTF8

[Mysqld]

# General #

Default_storage_engine = InnoDB

#character-set-server = UTF8

#collation-server = Utf8_unicode_ci

Basedir =/picclife/mysql

DataDir =/picclife/data

Socket =/tmp/mysql.sock

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

port=3306

# SAFETY #

Skip_name_resolve

Max_allowed_packet = 16M

Max_connect_errors = 100000

Lower-case-table-names = 1

# BINARY LOGGING #

Server-id = 1 #server ID

Log_bin = Mysql-bin #开启二进制日志

Relay_log = Relay-bin #开启中级日志

Expire_logs_days = 14

Sync_binlog = 1

Binlog_format = ROW

Transaction_isolation = read-committed

Relay_log_info_repository = TABLE

Master_info_repository = TABLE

# REPLICATION #

Gtid_mode = On

Enforce_gtid_consistency = On

Log_slave_updates = 1

#SEMI_SYNC

#rpl_semi_sync_master_enabled =1

#rpl_semi_sync_master_timeout =10000 # 1 Second

# CACHES and LIMITS #

Tmp_table_size = 256M

Max_heap_table_size = 256M

Query_cache_type = 0

Query_cache_size = 0

Max_connections = 5000

thread_cache_size = 1000

Open_files_limit = 65535

Table_definition_cache = 2048

Table_open_cache = 2048

Sort_buffer_size = 2M

Sql_mode = No_engine_substitution

# INNODB #

Innodb_flush_method = O_direct

Innodb_log_files_in_group = 2

Innodb_log_file_size = 256M

Innodb_flush_log_at_trx_commit = 1

innodb_file_per_table = 1

Innodb_buffer_pool_size = 10G

Innodb_stats_on_metadata = 0

Innodb_buffer_pool_instances = 4

# LOGGING #

Log_error =/picclife/data/mysql-error.log

#log_queries_not_using_indexes = 1

Slow_query_log = 1

Slow_query_log_file =/picclife/data/mysql-slow.log

Long_query_time = 2

log_error_verbosity=2

Wait_timeout = 7200

[Mysqldump]

User=root

password=123456

Installing the Database

./bin/mysqld--initialize--user=mysql--basedir=/picclfie/mysql--datadir=/picclife/data--innodb_undo_tablespaces =3--explicit_defaults_for_timestamp

Create a startup file

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

Modify/etc/init.d/mysql

Add to boot startup item

Chkconfig--add mysqld

Set boot up

Chkconfig mysqld on

Turn on MySQL

Service mysqld Start

Startup can also

(

Database General start and stop

mysqladmin -uroot -proot shutdown

mysqld_safe --defaults-file=/etc/my.cnf &

ps -ef|grep mysql

)

View the default password

Grep-i Password/picclife/data/mysql-error.log

Log in to the database with the initial password to modify the password

Mysql-uroot-p found the password.

Change Password

SET password=password (' password ');

Flush privileges;

And then launch the new password to log in.

The master configuration file has been modified to enter the database authorization

Permissions to copy from the server slave:

Grant Replication Slave on * * to [email protected] "from IP" identified by "123";

Refresh permissions:

Glush privileges;

To view the state of the master server Master has been given the binary name and location:

Show master status;

The master is configured to start the configuration from:

From the binary installation MySQL and live the same

That is, the server ID in the configuration file cannot be conflicting. You can turn on the binary log or do not turn it on, but you must turn on the trunk log relay_log = Mysql-relay

Vim/etc/my.cnf

[Client]

Port = 3306

Socket =/tmp/mysql.sock

# #default-character-set = UTF8

[MySQL]

Port = 3306

Socket =/tmp/mysql.sock

#default-character-set = UTF8

[Mysqld]

# General #

Default_storage_engine = InnoDB

#character-set-server = UTF8

#collation-server = Utf8_unicode_ci

Basedir =/picclife/mysql

DataDir =/picclife/data

Socket =/tmp/mysql.sock

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

port=3306

# SAFETY #

Skip_name_resolve

Max_allowed_packet = 16M

Max_connect_errors = 100000

Lower-case-table-names = 1

# BINARY LOGGING #

Server-id = 2 #server ID

Log_bin = Mysql-bin #开启二进制日志

Relay_log = Relay-bin #开启中级日志

Expire_logs_days = 14

Sync_binlog = 1

Binlog_format = ROW

Transaction_isolation = read-committed

Relay_log_info_repository = TABLE

Master_info_repository = TABLE

# REPLICATION #

Gtid_mode = On

Enforce_gtid_consistency = On

Log_slave_updates = 1

#SEMI_SYNC

#rpl_semi_sync_master_enabled =1

#rpl_semi_sync_master_timeout =10000 # 1 Second

# CACHES and LIMITS #

Tmp_table_size = 256M

Max_heap_table_size = 256M

Query_cache_type = 0

Query_cache_size = 0

Max_connections = 5000

thread_cache_size = 1000

Open_files_limit = 65535

Table_definition_cache = 2048

Table_open_cache = 2048

Sort_buffer_size = 2M

Sql_mode = No_engine_substitution

# INNODB #

Innodb_flush_method = O_direct

Innodb_log_files_in_group = 2

Innodb_log_file_size = 256M

Innodb_flush_log_at_trx_commit = 1

innodb_file_per_table = 1

Innodb_buffer_pool_size = 10G

Innodb_stats_on_metadata = 0

Innodb_buffer_pool_instances = 4

# LOGGING #

Log_error =/picclife/data/mysql-error.log

#log_queries_not_using_indexes = 1

Slow_query_log = 1

Slow_query_log_file =/picclife/data/mysql-slow.log

Long_query_time = 2

log_error_verbosity=2

Wait_timeout = 7200

The rest of the press claw you install the same as the master

Enter the database

Enter the database:

Close slave:

Stop slave;

Set the host user binary name location for master, and so on:

Change Master to master_host= "master's IP", master_user= "Tom", mster_password= "123",

Master_log_file= "mysql-bin.000002", master_log_pos=106;

Open slave

Start slave;

To view the status of slave:

Now that the master-slave copy is complete, you can create the library. View from top

Note: If the above configuration file starts an error, use the bottom-most configuration

Binary MySQL installation 5.7.20 configuration file

Vim/etc/my.cnf

[Client]

Port = 3306

Socket =/tmp/mysql.sock

[Mysqld]

Server_id=1

Port = 3306

user = MySQL

Character-set-server = Utf8mb4

Default_storage_engine = InnoDB

Log_timestamps = SYSTEM

Socket =/tmp/mysql.sock

Basedir =/picclife/mysql

DataDir =/picclife/data

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

Max_connections = 5000

Max_connect_errors = 10000

Table_open_cache = 2048

Max_allowed_packet = 16M

Open_files_limit = 65535

####====================================[innodb]==============================

Innodb_buffer_pool_size = 10G

innodb_file_per_table = 1

Innodb_write_io_threads = 4

Innodb_read_io_threads = 4

Innodb_purge_threads = 2

Innodb_flush_log_at_trx_commit = 1

Innodb_log_file_size = 512M

Innodb_log_files_in_group = 2

Innodb_log_buffer_size = 16M

innodb_max_dirty_pages_pct = 80

Innodb_lock_wait_timeout = 30

Innodb_data_file_path=ibdata1:1024m:autoextend

Innodb_undo_tablespaces=3

#####====================================[log]==============================

Log_error =/picclife/data/mysql-error.log

Slow_query_log = 1

Long_query_time = 2

Slow_query_log_file =/picclife/data/mysql-slow.log

Sql_mode=no_engine_substitution

Binary installation MySQL database