High Availability MySQL Reading Notes 1-MySQL installation in Windows

High Availability MySQL Reading Notes 1-MySQL installation in Windows

Recently, I have been reading O's REILIY series "high-availability MySQL", which naturally involves Master-Slave configuration and horizontal scaling. Master-Slave is a standard in many companies. It is usually used daily in development and has many problems (for example, Master nodes are not synchronized and Master nodes are down ), however, the operation permission is very limited. Some things can be better grasped only when they are read and done by themselves.

This is the First Reading Note of high-availability MySQL. It mainly records the installation process of compressed MySQL (Installation-based installation is not mentioned here) in Windows.

1. Download the compressed version of MySQL from the official website.

For: http://www.mysql.com/downloads/

Now, after oracle takes over MySQL, it seems that all resources can be downloaded only with an oracle account. For those who are used to clicking and downloading, it is indeed quite uncomfortable.

My downloaded file is: Mysql-advanced-5.6.21-win32.zip

This version of MySQL is very large after decompression, about 1.6 GB. It is impressive that all MySQL 5.1 files installed on the machine plus database files of several projects are only MB.

The decompressed folder contains the following subdirectories:

Where:

Bin directory-stores various executable MySQL programs

Data DIRECTORY-stores database data files and index files

MySQL-test-there are many written test scripts

SQL-benchmark-script for MySQL benchmark benchmarking

Bin, data, include, lib, and scripts directories are very important for the normal operation of MySQL.

About how to extract the directory:

Many people like to decompress MySQL to the Program files Folder. I do not recommend this because the Program files folder contains spaces, which may cause problems in many cases. For example, some programs may end with spaces as directories. We recommend that you extract the Directory D:/Mysql/, or store it in another directory, such as D:/LAMP/Mysql.

2. Configure Environment Variables

Open the system environment variable and append it to the Path variable (note that the append is not overwrite) MySQL bin directory:

　　

The blue part should be the complete path of your bin directory. After configuration, save and close the system Variable Window.

3. Configure the MYSQL configuration file my. ini

MySQL of the normal installation version will generate four ini configuration files with different configurations in the root directory of MySQL:

　　my-small.ini
　　my-medium.ini
　　my-large.ini
　　my-huge.ini

These configuration files are roughly the default configurations given by the database scale. You can copy and generate the actual my. ini configuration file according to your actual situation. Some versions also generate configuration files only for Innodb, which will not be described here.

After decompressing MySQL for this compressed version, there is only one default configuration file in the root directory: my-default.ini. After the configuration is enabled, the configuration options are extremely simple:

 

In addition, there is no other (SWEAT ).

Our configuration starts from this:

(1) Configure basedir

Basedir refers to the root directory of mysql, so it should be: basedir = D: \ MYSQL

(2). configure data

The Data directory is the directory for storing Data files and index files. You can specify a directory other than the mysql directory as the storage directory, but pay attention to permission issues. My configuration is: datadir = D: \ MYSQL \ data

(3) configure the port

Mysql usually uses port 3306. If the port is occupied, you can replace other ports.

(4). More configurations

Set the client mode character: default-character-set = utf8. For this question, a blog post will be devoted to this issue.

Default table storage reference: default-storage-engine = INNODB. By the way, in the old mysql release, the default storage engine is MyIsam, and the new version is the default INNoDB Storage engine (the specific version, I also don't remember: D. You can check changelog To Find Out)

For more configurations, see the MySQL manual.

4. Install and start the MySQL Service

Run mysqld-install Mysql in the bin directory of mysql (remember to run cmd as an administrator)

Start the mysql service: net start mysql (or right-click the computer to manage it-> services-> mysql right-click to start it)

5. Modify the MySQL user account

In this case, you can use the command line to connect to MySQL in cmd.

After MySQL is installed by default, a root user and an anonymous user are generated. We recommend that you modify the password of the root account and delete the anonymous user as follows:

　Use test;　Select Host,User,Password from user;

(1) Delete Anonymous Users:

　delete from user where User=’’;

(2) change the password of the root account:

update user set Password=PASSWORD(‘123456’) where User=’root’;

(3) add Mysql remote connection Permissions

grant all privileges on *.* to root@'%' identified by '123456';

(4) Refresh Permissions

flush privileges;

The User table is a Mysql built-in table used to record User permissions, passwords, and other information. The primary key of this table is Host + User, which can be seen through the show index command:

 

The field ending with _ priv is the Mysql operation permission of the corresponding account, and the filed-type is enum.

Now, even if the Mysql configuration is complete, the Mysql master-slave configuration will be added later. The journey to high availability Mysql began.

After extracting the latest mysql package, there are a bunch of files without any installation files. Do you not need to install it? I downloaded a mysql-5529-winx64zip.

The Installation Wizard is available only when you download msi. you can install the compressed files in batches! I forgot the details.

Mysql-5521-win32zip compressed package detailed installation diagram, in Baidu Library to see a related article, some places are not the same

1. Use notepad to create my. ini, leave it blank, or change the name of another ini to my. ini.

The following operations need to go to the cmd command line
2. Add a windows Service
E: \ mysql-5.5.21-win32 \ bin> mysqld -- install "MySQL"
Service successfully installed.
3. Start mysql
Net start mysql
Check whether port 3306 is enabled
Netstat-an | find "3306"
4. Stop mysql
Net stop mysql
5. delete a service
E: \ mysql-5.5.21-win32 \ bin> mysqld -- remove "MySQL"
Service successfully removed.