MySQL installation and master-slave configuration

Source: Internet
Author: User
Tags mysql version unique id

installation Mysql

Operating System: Windows Server R2 Standard

MySQL version: mysql-5.7.17-winx64

First step: Unzip mysql-5.7.17-winx64 . zip to the specified location

The Second step: Add the Data folder under the root directory of the installation file and My-default.ini renamed to My.ini

Step three: At the command prompt, enter the following command:

Note that if you have previously installed, to uninstall the service, use the command:mysqld-remove

Execute the following command in turn:

CD C:\mysql-5.7.17-winx64\bin

Mysqld--initialize (Note that there is only one space between mysqld and--)

Mysqld--install

Uninstall the service and then execute the install command because it was previously installed

Fourth Step: Modify the My.ini file , add a row under [mysqld] skip-grant-tables , and then in turn Line

Start MySQL service : net start MySQL

Enter MySQL:MySQL

Set Password:update mysql.user Set authentication_string = password (' cnki2017 ') where ' user ' = ' root ';(note there are semicolons, Note After copying the command, you may want to adjust the input method and then re-enter it.

Note: Set the password here you can split the two-step command:

Use MySQL;

Update user set Authentication_string=password ("cnki2017") where user= "root";

Refresh MySQL System permissions related table :flush privileges; (note there is a semicolon)

Exit MySql:exit; (note there is a semicolon)

stop mysql service:net stop MySQL

Fifth Step: Delete My.ini file to add the previously added Skip-grant-tables , and then execute the following command in turn:

Start MySQL service : net start MySQL

Enter MySQL:mysql-uroot-pcnki2017

Reset Password:set Password=password (' cnki2017 ');(note a semicolon)

allow any IP login:update mysql.user set host = '% ' where user = ' root ' limit 1; (note there is a semicolon)

Refresh MySQL System permissions related table :flush privileges; (note there is a semicolon)

Exit MYSQL:exit (can have no semicolon)

Sixth Step: Configure

Character Set configuration: execute the following command sequentially

CD C:\mysql-5.7.17-winx64\bin

mysql-uroot-pcnki2017

Show variables like ' character% ';

Set Variable_name=utf8;

which Variable_name replaced with the corresponding name, for example, variable_name for Character_set_database, then the command is: set Character_set_database=utf8;

even if you set the above command 5.7 does not work

to add the following in My.ini:

[Mysqld]

Character-set-server=utf8

[Client]

Default-character-set=utf8

Set the default engine

[Mysqld]

Default-storage-engine=innodb

Note:

To create a new user, execute the following command in turn:

mysql-uroot-pcnki2017

Create user [email protected] '% ' identified by ' password ';

GRANT all privileges on * * to ' username ' @ ' percent ' identified by ' password ' with GRANT OPTION;

Flush privileges;

Username and password for the user name and password to be created in the above command

Master-slave replication

Operating System: Windows Server R2 Standard

MySQL version: mysql-5.7.17-winx64

Main ip:192.168.107.13

from ip:192.168.107.73

Configure Primary ( Master)

The first step is to configure My.ini, and then restart the server

Server-id=1

Log-bin=mysql-bin

Binlog-do-db=hy_webapi

Meaning analysis:

Server-id: unique ID, unique number of the server.

Log-bin: Specifies that binary logging is turned on.

Binlog-do-db: library to master and slave synchronization

Second Step authorization

Command Prompt operation

CD to bin directory:CD C:\mysql-5.7.17-winx64\bin

Enter MYSQL :mysql-uroot-pcnki2017

Execute Show command :show Master status; record the above File and Position.

Enable master-slave replication users : GRANT REPLICATION SLAVE on * * to ' root ' @ ' 192.168.107.73 ' identified by cnki2017; ( note there is a semicolon )

Note: This can be assigned to a different account from the library without using the root account, provided that the account is created first.

Configure the slave server

The first step is to configure My.ini, and then restart the server

Server-id =2

Replicate-do-db=hy_webapi

Relay-log-index=slave-relay-bin.index

Relay-log=slave-relay-bin

which

Relay-log: define the location and name of the Relay_log

Relay-log-index: index file for relay log

Second Step authorization

Command Prompt operation

CD to bin directory:CD C:\mysql-5.7.17-winx64\bin

Enter MYSQL :mysql-uroot-pcnki2017

Set permissions:change master to master_host= ' 192.168.107.13 ', master_user= ' root ', master_password= ' cnki2017 ', Master_log_file= ' mysql-bin.000001 ', master_log_pos=154;

Open from library:start slave;

View information:show slave status \g;

Note: Here, when setting permissions, you can assign other accounts to the main library without using the root account, provided that the account is created first.

-----------------------------------------------------------------------------------------

Reprint and quote please specify the source.

Time haste, the level is limited, if has the improper place, welcome correction.

MySQL installation and master-slave configuration

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.