Amazon EC2 installs MySQL multi-instance and configures master-slave replication

1. mysql Installation:

EC2 installing MySQL directly with Yum is easy:

Yum install MySQL mysql-server mysql-libs
There are three packages installed here

Can be used directly after the finished
Service mysqld Start
Start the MySQL service

Change root password:
Mysqladmin-u root-p Password "root"

2. Install multi-instance MySQL using Mysqld_multi:

Vim/etc/my.cnf
Adding configuration information to an instance

[Mysqld]
Datadir=/var/lib/mysql
Socket=/var/lib/mysql/mysql.sock
Symbolic-links=0

[Mysqld_safe]
Log-error=/var/log/mysqld.log
Pid-file=/var/run/mysqld/mysqld.pid

The above is the default configuration information, which is added under mysqld such as the following configuration:

#开启慢查询日志, log queries for too long SQL statements. For easy optimization

Log_slow_queries=/var/log/mysql/mysql-slow.log

#开启 bin-log logs without log-bin=xxx. It was not possible to start the MySQL service at the time of the experiment.

Log-bin

#分配serverid. Do not conflict with the following from the server can be

Server-id=1

At the end of the document, add:

[Mysqld_multi]
Mysqld =/usr/bin/mysqld_safe
Mysqladmin =/usr/bin/mysqladmin
user = root
Password = root

#第二个mysql实例的配置

[Mysqld1]
Socket =/tmp/mysql1.sock
Port = 3311
Pid-file =/var/run/mysqld/mysqld1.pid
Log-slow-queries =/var/log/mysql1/slow-query-log # # MySQL Slow log
Relay-log =/var/spool/mysqld1/mysqld-relay-bin # # Relay for replication
DataDir =/var/lib/mysql1 # # mysql data file folder
user = MySQL
Log-bin
server-id=2

Run after saving exit
Mysql_install_db–user=mysql–datadir=/var/lib/mysql1
Complete initialization of the second instance

The instance service is started as follows:
Mysqld_multi Start 1

Use the command to see if 3311port is turned on
Netstat-tunlp
Assuming there is a successful start

Landing Change Example
Mysql-s/tmp/mysql1.sock-uroot-p

To change the root password also specify the sock file:
Mysqladmin-s/tmp/mysql1.sock-u root-p Password "root"

Here with Mysql-uroot-p-p3311 landed in the default 3306port. So use the above method to log in

Can be used after entering
Show variables like '%datadir% ';
See if DataDir is the path in the configuration file

3. Configure master-slave replication

Enter the primary database (default 3306port)
Mysql-uroot-proot

View User Login Information
Select User,host from Mysql.user;

Set the root user to be able to log in from anywhere, no matter what table
Grant all on . to [email protected] '% ' identified by ' root ' with GRANT option;

Create a new user backup to log in from the database. Get the master database information, password is also backup (here the login address is localhost, assuming from the database on other machines need to change to the corresponding IP)
GRANT REPLICATION SLAVE on . "Backup" @ ' localhost ' identified by ' backup ';

Use
Show variables like "%log_%";
To see if the binary log is on (log-bin in the config file), to use master-slave replication, you must turn on this

Use
Show master status;
View Primary Database Information
File and Position
These two messages will be used when the configuration is from the database.

Enter from the library, run the following command to configure the main library information

Change master to master_host= ' localhost ', master_user= ' backup ', master_password= ' backup ', master_log_file= ' Mysqld-bin.000003 ', master_log_pos=107;

Master_host: Main Library address (assuming not the default of 3306 to specify port information using Master_port)
Master_user: The user configured above to log in to the main library from the library
Master_log_file: The main library show master status; The value of file in
Master_log_pos: The main library show master status; position value

The following error message appears:
ERROR 1201 (HY000): Could not initialize master info structure; More error messages can is found in the MySQL error log

Workaround:

Stop slave;
Reset slave;
Error again:
ERROR (HY000): File '/var/spool/mysqld1/mysqld-relay-bin.index ' not Found (errcode:13)

Create the appropriate file and change permissions
sudo mkdir var/spool/mysqld1
sudo touch/var/spool/mysqld1/mysqld-relay-bin.index
sudo chown-r mysql:mysql/var/spool/mysqld1/

Again, reset slave can

Open from Library
Start slave;

View status information
Show Slave Status\g

Among the
Slave_io_running:yes
Slave_sql_running:yes
Both must be yes to be successful

If the error is indicated:
Fatal error:the slave I/O thread stops because master and slave have equal MySQL server IDs

The ServerID is not well-equipped. Can be corrected directly by command

View the current server_id
Show variables like ' server_id ';
Set to another value again
Set global server_id=2;

Once again, you can start the slave.

Then we can do some test work, the establishment of a new database on the main library or a new table or add changes to the record will be synchronized to the library

4, MySQL some other unusual solution

The following error occurred using MySQL:
Mysqladmin:can ' t turn off logging; Error: ' Access denied; You need the SUPER privilege for this operation ' error

Workaround:
Vim/etc/my.cnf

Join
Skip-grant-tables
Restart Service

Go to MySQL

Use MySQL;
UPDATE user SET Password = Password (' pwd ') WHERE user = ' root ';
Flush privileges;

After exiting
MY.CNF, change it back.
Restart Service

Assuming there's no effect.
To uninstall MySQL:

Uninstalling yum-y Remove mysql*
Check to see if Yum List installed is installed mysql*
Delete a MySQL-related folder
Rm-rf/etc/my.cnf
Rm-rf/var/lib/mysql

Install MySQL again later

Telnet
Error occurred
Host ' 117.29.168.34 ' isn't allowed to connect to this MySQL server

Workaround:
Root login MySQL
Run:
Use MySQL;
Update user Set host = '% ' where user= ' root ';
Flush privileges;

Amazon EC2 installs MySQL multi-instance and configures master-slave replication