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

Mysql real environment building master-slave

Security code: the reason why people can, is to believe that they can.

Nowadays, there are many kinds of databases, such as oracle mysql SQL Server ACCESS, etc. Today we come to the real environment to build mysql master and slave. Let's first talk about the differences between the four databases:

1. The difference between the four databases:

ACCESS: the function is relatively not so powerful, mainly used in the development of stand-alone software.

SQL Server: it is a widely used and popular database at present, and it is a milestone in the development of database.

MySQL: an open source relational database management system that is fast, reliable, and easy to use; MySQL servers work in client / server or embedded systems.

The function of Oracle:Oracle is relatively powerful, and it is generally used for the establishment of super-large management system software. Oracle has good compatibility, portability, connectivity and high productivity so that Oracle RDBMS has good openness. Now the scope of application has been relatively wide.

Let's build the master and slave:

Operating system centos6.5_64

Database version mysql5.6.29

Main database 192.168.226.133

From database 192.168.226.134

Mysql synchronization account root/root

One. Prepare to install mysql

1. Download and upload mysql source code package mysql-5.6.29.tar.gz

Http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.29.tar.gz

2. Check and uninstall the lower version of mysql on the system

Rpm-qa | grep mysql

Yum remove mysql mysql-server mysql-libs

3. Install the necessary system plug-ins

Yum install ncurses-deve cmake

Or

Rpm-ivh ncurses-devel-5.7-3.20090208.el6.x86_64.rpm

Rpm-ivh cmake-2.6.4-5.el6.x86_64.rpm

Second, add users and groups:

Add users and groups:

Groupadd mysql

Useradd-g mysql mysql-s / usr/sbin/nologin

Third, create catalogue and authorization

Mkdir-p / data/mysql/data

Mkdir-p / data/mysql/log/iblog

Mkdir-p / data/mysql/log/binlog

Mkdir-p / data/mysql/log/relaylog

Mkdir-p / data/mysql/run

Mkdir-p / data/mysql/tmp

Chown-R mysql:mysql / data/mysql

Chmod-R 755 / data/mysql

IV. Start compiling

1. Decompression

Tar zxf mysql-5.6.29.tar.gz

Cd mysql-5.6.29

Compile and install mysql:

Cmake\

-DCMAKE_INSTALL_PREFIX=/data/mysql\

-DINSTALL_DATADIR=/data/mysql/data\

-DDEFAULT_CHARSET=utf8\

-DDEFAULT_COLLATION=utf8_general_ci\

-DEXTRA_CHARSETS=all\

-DWITH_SSL=yes\

-DWITH_EMBEDDED_SERVER=1\

-DENABLED_LOCAL_INFILE=1\

-DWITH_MYISAM_STORAGE_ENGINE=1\

-DWITH_INNOBASE_STORAGE_ENGINE=1\

-DWITH_ARCHIVE_STORAGE_ENGINE=1\

-DWITH_BLACKHOLE_STORAGE_ENGINE=1\

-DWITH_FEDERATED_STORAGE_ENGINE=1\

-DWITH_PARTITION_STORAGE_ENGINE=1\

-DMYSQL_TCP_PORT=3306\

-DENABLED_LOCAL_INFILE=1\

-DSYSCONFDIR=/etc\

-DWITH_READLINE=on

Make

Make install

5. Modify the configuration file my.cnf parameters

Cp / data/mysql/support-files/my-default.cnf / data/mysql/my.cnf

Modify the master database configuration file my.cnf

Vi / data/mysql/my.cnf

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

# * * DO NOT EDIT THIS FILE. It's a template which will be copied to the

# * * default location during install, and will be replaced if you

# * * upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128m

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

# These are commonly set, remove the # and set as required.

# basedir =.

# datadir =.

# port =.

# server_id =.

# socket =.

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128m

# sort_buffer_size = 2m

# read_rnd_buffer_size = 2m

Sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[client]

Port = 3306

Socket = / data/mysql/run/mysql.sock

[mysql]

No-auto-rehash

[mysqld]

# # enable autocommit

Autocommit=1

General_log=off

Explicit_defaults_for_timestamp=true

# system

Basedir=/data/mysql

Datadir=/data/mysql/data/

Max_allowed_packet=134217728

Max_connections=8192

Max_user_connections=8000

Open_files_limit=65535

Pid_file=/data/mysql/run/mysqld.pid

Port=3306

Server_id=128

Skip_name_resolve=ON

Socket=/data/mysql/run/mysql.sock

Tmpdir=/data/mysql/tmp

# binlog

Binlog_cache_size=32768

Binlog_format=row

Expire_logs_days=15

Log-bin=/data/mysql/log/binlog/master-bin

Log-bin-index=/data/mysql/log/binlog/master-bin.index

Log_slave_updates=ON

Max_binlog_cache_size=2147483648

Max_binlog_size=524288000

Sync_binlog=100

# relay

# LOGGING #

Log_error = / data/mysql/log/alert.log

Log_queries_not_using_indexes = 1

Slow_query_log = 1

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

Log_slave_updates=ON

Log_slow_admin_statements=1

Long_query_time=1

# slave#

Slave_skip_errors=OFF

Log_slave_updates=ON

# innodb #

Innodb_log_group_home_dir=/data/mysql/log/iblog

Innodb_data_home_dir=/data/mysql/log/iblog

Innodb_adaptive_flushing=1

Innodb_additional_mem_pool_size=20M

Innodb_buffer_pool_instances=8

Innodb_change_buffering=inserts

Innodb_data_file_path=ibdata1:32M;ibdata2:16M:autoextend

Innodb_flush_method = O_DIRECT

Innodb_log_files_in_group = 4

Innodb_log_file_size = 100m

Innodb_flush_log_at_trx_commit = 1

Innodb_file_per_table = 1

Innodb_buffer_pool_size = 128m

Innodb_file_format=Barracuda

Innodb_file_io_threads=4

Innodb_flush_neighbors=0

Innodb_io_capacity=200

Innodb_lock_wait_timeout=5

Innodb_log_buffer_size=64M

Innodb_lru_scan_depth=2048

Innodb_max_dirty_pages_pct=60

Innodb_old_blocks_time=1000

Innodb_online_alter_log_max_size=200M

Innodb_open_files=200

Innodb_print_all_deadlocks=1

Innodb_purge_threads=4

Innodb_read_ahead_threshold=0

Innodb_read_io_threads=8

Innodb_rollback_on_timeout=0

Innodb_sort_buffer_size=2M

Innodb_spin_wait_delay=6

Innodb_stats_on_metadata=0

Innodb_strict_mode=1

Innodb_sync_array_size=256

Innodb_sync_spin_loops=30

Innodb_thread_concurrency=64

Innodb_use_native_aio=0

Innodb_write_io_threads=8

Innodb_support_xa=1

[mysqld_safe]

Datadir=/data/mysql/data/

Modify the my.cnf configuration file from the database:

Vi / data/mysql/my.cnf

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

# * * DO NOT EDIT THIS FILE. It's a template which will be copied to the

# * * default location during install, and will be replaced if you

# * * upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128m

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

# These are commonly set, remove the # and set as required.

# basedir =.

# datadir =.

# port =.

# server_id =.

# socket =.

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128m

# sort_buffer_size = 2m

# read_rnd_buffer_size = 2m

Sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[client]

Port = 3306

Socket = / data/mysql/run/mysql.sock

[mysql]

No-auto-rehash

[mysqld]

# # enable autocommit

Autocommit=1

General_log=off

Explicit_defaults_for_timestamp=true

# system

Basedir=/data/mysql

Datadir=/data/mysql/data/

Max_allowed_packet=134217728

Max_connections=8192

Max_user_connections=8000

Open_files_limit=65535

Pid_file=/data/mysql/run/mysqld.pid

Port=3306

Server_id=240

Skip_name_resolve=ON

Socket=/data/mysql/run/mysql.sock

Tmpdir=/data/mysql/tmp

# binlog

Binlog_cache_size=32768

Binlog_format=row

Expire_logs_days=15

Log-bin=/data/mysql/log/binlog/slave-bin

Log-bin-index=/data/mysql/log/binlog/slave-bin.index

Log_slave_updates=ON

Max_binlog_cache_size=2147483648

Max_binlog_size=524288000

Sync_binlog=100

# relay

Relay-log=/data/mysql/log/relaylog/slave-relay-bin

Relay-log-index=/data/mysql/log/relaylog/slave-relay-bin.index

# LOGGING #

Log_error = / data/mysql/log/alert.log

Log_queries_not_using_indexes = 1

Slow_query_log = 1

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

Log_slave_updates=ON

Log_slow_admin_statements=1

Long_query_time=1

# slave#

Slave_skip_errors=OFF

Log_slave_updates=ON

# innodb #

Innodb_log_group_home_dir=/data/mysql/log/iblog

Innodb_data_home_dir=/data/mysql/log/iblog

Innodb_adaptive_flushing=1

Innodb_additional_mem_pool_size=20M

Innodb_buffer_pool_instances=8

Innodb_change_buffering=inserts

Innodb_data_file_path=ibdata1:32M;ibdata2:16M:autoextend

Innodb_flush_method = O_DIRECT

Innodb_log_files_in_group = 4

Innodb_log_file_size = 100m

Innodb_flush_log_at_trx_commit = 1

Innodb_file_per_table = 1

Innodb_buffer_pool_size = 128m

Innodb_file_format=Barracuda

Innodb_file_io_threads=4

Innodb_flush_neighbors=0

Innodb_io_capacity=200

Innodb_lock_wait_timeout=5

Innodb_log_buffer_size=64M

Innodb_lru_scan_depth=2048

Innodb_max_dirty_pages_pct=60

Innodb_old_blocks_time=1000

Innodb_online_alter_log_max_size=200M

Innodb_open_files=200

Innodb_print_all_deadlocks=1

Innodb_purge_threads=4

Innodb_read_ahead_threshold=0

Innodb_read_io_threads=8

Innodb_rollback_on_timeout=0

Innodb_sort_buffer_size=2M

Innodb_spin_wait_delay=6

Innodb_stats_on_metadata=0

Innodb_strict_mode=1

Innodb_sync_array_size=256

Innodb_sync_spin_loops=30

Innodb_thread_concurrency=64

Innodb_use_native_aio=0

Innodb_write_io_threads=8

Innodb_support_xa=1

[mysqld_safe]

Datadir=/data/mysql/data/

Execute the MySQL installation script

Cd / data/mysql/

. / scripts/mysql_install_db-- defaults-file=/data/mysql/my.cnf-- user=mysql-- datadir=/data/mysql/data

7. Modify system environment variables

Vi / etc/profile

PATH=/data/mysql/bin:$PATH:/sbin

8. Self-starting script

Cp / data/mysql/my.cnf / etc/my.cnf

Cp / data/mysql/support-files/mysql.server / etc/init.d/mysql

Vi / etc/init.d/mysql

Modify mysqld_pid_file_path=/data/mysql/run/mysqld.pid

Chkconfig-add mysql

/ etc/init.d/mysql start

/ etc/init.d/mysql stop

The master makes the corresponding configuration from the above as the corresponding configuration.

IX. Configure the master and slave

Log in to the database on the main server and configure the database

Mysql-h227.0.0.1-uroot-P3306

Note: if this report is wrong.

Reason: this is because the system will look for the command under / usr/bin by default. If the command is not in this directory, of course you can't find it. All we need to do is map a link to the / usr/bin directory, which is equivalent to creating a link file.

First of all, we need to know the full path of the mysql command or mysqladmin command, for example, the path of mysql is: / usr/local/mysql/bin/mysql, we can execute the command like this:

Ln-s / data/mysql/bin/mysql / usr/bin

The problem can be solved.

Mysql > set old_passwords=0

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

Mysql > SHOW MASTER STATUS

+-+

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

+-+

| | master-bin.000003 | 333 | |

+-+

1 row in set (0.00 sec)

You need to use the two parameters marked red above.

Log in to the database from the server and configure the database

Mysql-h227.0.0.1-uroot-P3306

Mysql > set old_passwords=0

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

Mysql > stop slave

Mysql > CHANGE MASTER TO MASTER_HOST='10.140.19.189',MASTER_USER='root',MASTER_PASSWORD='root', MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=333

Mysql > start slave

Mysql > show slave status\ G

In the printed state, view the values of the following two parameters:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

If the values of both parameters are yes, it works fine, and you can add a database or a table on the master database to check if it exists on the slave database.

If either of these parameters is not Yes, there is a problem. You can view the database error log file on slave to see the cause of the error.

Common causes of errors

1. Server-id is the same.

2. User rights are not enough. The required permissions include: REPLICATION SLAVE,RELOAD,CREATE USER,SUPER.

GRANT REPLICATION SLAVE,RELOAD,CREATE USER,SUPER ON *. * TO {USER} @ {IP_ADDR} WITH GRANT OPTION

3. The database UUID is consistent. If mysql is installed through batch copy, it is possible that the UUID of the database is the same, enter the datadir directory of the database, and modify auto.cnf. Just change the value in uuid at will and restart the mysqld service.

4. The Master database port is blocked by the firewall.

I hope I can help you! 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.