MySQL Series III: Installation of MySQL and MySQL master-slave replication under CENTOS6

First, install MySQL under CENTOS6

Check if the system has its own Mysql:yum list installed | grep MySQL,
If there is already, execute the command yum-y remove mysql-libs.x86_64 Uninstall the MySQL that is already installed.

1. System conventions

Installation file download directory:/data/software
MySQL Directory Installation location:/usr/local/mysql
Database Save location:/data/mysql
Log Save location:/data/log/mysql

2. Download MySQL

Perform the following naming:

- /Data//data/software

--Download the installation package

In the official website: http://dev.mysql.com/downloads/mysql/, select the following version of MySQL download:

wget http://dev.mysql.com/get/Downloads/mysql-5.7/ MySQL-5.7.  - -Linux-GLIBC2. 5 -x86_64.tar.gz

Note: If you are prompted Bash:wget:command not found, execute yum-y install wget, and then execute the wget download command

3. Unzip the package to the target location

/Data/software

--Unzip the package

- /software/mysql-5.7.  - -Linux-GLIBC2. 5 -x86_64.tar.gz

--Move and modify file names

/software/mysql-5.7.  - -Linux-GLIBC2. 5 - /usr/local/MySQL

4. Create a Data Warehouse catalog

- /Data/MySQL    

5. Create a new MySQL user, group, and directory

- - /sbin/--/usr/local/MySQL     --  -New MSYQL User Forbidden Login Shell

Description: If you are prompted Useradd: "MySQL" group does not exist, perform groupadd MySQL add group to execute later

6. Changes to the directory belong to the person

/usr/local/---/data /MySQL

7. Configuration parameters

Bin/--initialize--user=mysql--basedir=/usr/local/mysql--datadir=/data/mysql

Description

This could be a mistake.

Workaround: Yum install-y Libaio

Continue execution after resolving the error: # bin/mysqld--initialize--user=mysql--basedir=/usr/local/mysql--datadir=/data/mysql

Note the temporary password generated here, as shown at the end of this section:) u!7g! q3jz>&

Continue execution:

Bin/mysql_ssl_rsa_setup  --datadir=/data/mysql

8. Modify the system configuration file

/usr/local/MySQL/support-filescp my-Default  /etc//etc/init.d/MySQL

/etc/init.d/MySQL

Modify the following content:

9. Start and connect MySQL remotely

/etc/init.d/mysql start

--Log in to MySQL

- - -P

Description

If it appears:-bash:mysql:command not found

On execution: # ln-s/usr/local/mysql/bin/mysql/usr/bin--no need to execute without appearing

--Enter the temporary password generated by step 6th

--Change Password

Mysql> set Password=password (' 123456 ');

--Set the host address of the root account ( modified to connect remotely )

MySQL>Grantallprivilegeson*. *  to ' Root '@'%' by'123456'; MySQL>privileges;

--View user information

MySQL> use mysql;mysql>Select Host,user from user;

--You can use the remote connection test here;

MySQL Operation command

--Exit the MySQL command window

#exit

--View MySQL status

#service MySQL Status

--Stop MySQL

#service MySQL Stop

--Start MySQL

#service MySQL Start

Second, MySQL master-slave copy of the building

Environment Preparation:

First, the previous installation of the MySQL virtual machine to clone a slave node as MySQL, cloning method reference my article: learn the MySQL process to expand the other technology stack: Set up a Linux virtual machine fixed IP and clone Linux virtual machine

The main library is located on the server IP address of 192.168.168.130, from the server IP address of the library is 192.168.168.131

1. mysql master-slave replication principle

MySQL master-slave replication principle:

1). Master logs the operation to the binary log (binary logs);

2). Slave IO thread writes the binary log events of master to its trunk log (relay log);

3). Slave SQL thread reads the trunk log, which will redo the record data into the database.

MySQL Master-Slave synchronization is a very mature architecture with the following advantages:

1) in the slave server can perform query work (that is, we often say that the read function), reduce the primary server pressure;

2) in the backup from the primary server, to avoid affecting the primary server service during the backup;

3) When there is a problem with the primary server, you can switch to the slave server.

2. Main Library 192.168.168.130 settings

Modify the main library my.cnf, mainly set a different Server-id and Log-bin, red part of the new section.

Vim/etc/my.cnf

[Mysqld] datadir=/data/mysql#socket=/var/lib/mysql/mysql.sock#user=mysql# disabling Symbolic-links is Recommended to Prevent assorted security riskssymbolic-links=0log-bin=mysql-bin #需要启用二进制日志server-id=1 # Used to identify different database servers Binlog-ignore-db=information_schema #忽略记录二进制日志的数据库binlog-ignore-db=cluster # Ignores database binlog-ignore-db=mysql #忽略记录二进制日志的数据库 logging binary logs     

Restart the main library to take effect :

Service MySQL Restart

Log in to the main library:

Mysql-hlocalhost-uroot-p

Assign permissions from Vault account:

Grant all privileges on * * to ' user name ' @ '% ' identified by ' password ';

The account name created in this article is root and the password is 123456

To see if the user was created successfully:

Select User,host from Mysql.user;

Displays the main library information, records file and position, and the library settings will be used:

Show master status;

3. Setting from library 192.168.168.131

Modify the/etc/my.cnf from the library configuration file

Vim/etc/my.cnf

[Mysqld] datadir=/data/mysql#socket=/var/lib/mysql/mysql.sock#user=mysql# disabling Symbolic-links is Recommended to Prevent assorted security risks#symbolic-links=0log-bin=mysql-bin #需要启用二进制日志server-id=2 # Used to identify different database servers binlog-ignore-db=information_schema #忽略记录二进制日志的数据库binlog-ignore-db=Cluster # Ignore database logging binary log binlog-ignore-db=mysql #忽略记录二进制日志的数据库replicate-do-db=test #指定复制的数据库replicate-ignore-db= MySQL #不复制的数据库 #log-slave-updates #该从库是否写入二进制日志 and can be enabled if you need to become a multi-master. Read-only can not need #slave-skip-errors=allslave-net-timeout=60          

Restart from library takes effect :

Service MySQL Restart

Log in from the library:

Mysql-u root-p

Stop from Library:

Stop slave;

Connect master, master_log_file the Master Library's File,master_log_pos for the main library position:

Change Master to master_host= ' 192.168.152.130 ', master_user= ' root ', master_password= ' 123456 ', master_log_file= ' Mysql-bin.000002 ', master_log_pos=434;

Description

Master_log_file= ' mysql-bin.000002 ', master_log_pos=434 in the preceding command statement, corresponds to the show Master status previously performed in the main library; Results.

To start the service from the library:

Start slave;

4. Test whether master-slave replication is in effect

Create a new database test on the main library server, and then create a new table inside

Create database test; use test; CREATE TABLE ' myTest ' (     ' id ' INT (5) UNSIGNED not null auto_increment,     ' username ' VARCHAR (a) NOT NULL  , '     password ' CHAR (+) is not null, ' last_update ' DATETIME is not null, ' number ' FLOAT (Ten) is not NULL, ' Content ' TEXT not NULL , PRIMARY KEY (' id ')) ENGINE = MYISAM;        

If the new table is present from the library to view the main library:

You can see that the information from the library and the main library is consistent and the master-slave configuration succeeds. For further verification, enter show slave from the library status\g

Slave_io_running:yes (network Normal);

Slave_sql_running:yes (table structure Normal)

The correctness of the above process is further verified.

Reference article:

1. Install mysql:https://www.cnblogs.com/jr1260/p/6590232.html under CENTOS6

2. CentOS 6.5 under MySQL Master and master configuration: https://www.cnblogs.com/whutxldwhj/p/5970540.html

MySQL Series III: Installation of MySQL and MySQL master-slave replication under CENTOS6