MySQL master/slave configuration + read/write separation

MySQL starting with version 5.5, the compile configuration via./configure has been canceled and replaced by the CMake tool. Therefore, we first need to compile and install the CMake tool in the system source code.

Note: Before installing, be sure to check if the following dependencies are installed, if they are not installed.

Apt-get-y install gcc g++ libncurses5-dev ncurses-devel OpenSSL

A. Main Library installation and configuration

1. Source Installation CMake

# Tar XF cmake-3.0.0.tar.gz

# CD cmake-3.0.0

#./configure--prefix=/usr/local/cmake

# make && make install or gmake && gmake install

Note: Here must be installed with the source code, can not be installed with apt-get, or it will be in the compile times wrong.

2. compile and install MySQL

#mkdir-P/mysql/data/usr/local/mysql

#useradd-R-s/usr/sbin/nologin MySQL

#chown Mysql.mysql-r/mysql/data

# Tar XF mysql-5.5.21.tar.gz

# CD mysql-5.5.21

#/usr/local/cmake/bin/cmake-dcmake_install_prefix=/usr/local/mysql

-dmysql_datadir=/mysql/data/\

-dmysql_unix_addr=/mysql/data/mysqld.sock \

-dwith_innobase_storage_engine=1 \

-DSYSCONFDIR=/ETC \

-dextra_charsets=all \

-ddefault_charset=utf8 \

-DDEFAULT_COLLATION=UTF8_UNICODE_CI \

-dwith_debug=0# make && make install

Note: If there is a hint in the middle of the error can not continue, please include some of the dependencies on the package, and delete the Cmakecache file and then recompile;

Compile each parameter description:

-dcmake_install_prefix=/usr/local/mysql

; MySQL installed home directory, 5.5. Version 17 defaults to/usr/local/mysql, (can be omitted)

-dmysql_datadir=/home/system_username/mysql/mysqldata

; MySQL data save path, custom

-dmysql_unix_addr=/tmp/mysqld.sock

; System socket file (. sock) settings, socket connection based on the file path, absolute path required

-DSYSCONFDIR=/ETC MySQL; Config file my.cnf address, default/etc (can be omitted)

-dmysql_tcp_port=3306

; Listening port for the database server TCP/IP connection, default is 3306 (can be omitted)

-denabled_local_infile=1; Default is OFF, this opens

-dextra_charsets=all

-ddefault_charset=utf8

-ddefault_collation=utf8-general_ci; Database encoding Settings

-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; Storage Engine Settings

# CP SUPPORT-FILES/MY-MEDIUM.CNF/ETC/MY.CNF

# CP Support-files/mysql.server/etc/init.d/mysqld

# chmod +x/etc/init.d/mysqld

# CD scripts/

# sh mysql_install_db--user=mysql--basedir=/usr/local/mysql/--datadir=/mysql/data/

# ln/usr/local/mysql/bin/*/usr/local/sbin/

# service Mysqld Start

Add a password to root 123456

#/usr/local/mysql/bin/mysqladmin-u Root Password 123456

Use MySQL database/change the login password of the root user

# mysql-uroot-p 123456

> Use MySQL;

> Update user Set Password=password ("654321") where user= ' root ';

> Mysql>flush privileges;

3. Main Library configuration file my.cnf modification

# VIM/ETC/MY.CNF

Log-bin=master-bin

Log-bin-index=master-bin.index

Binlog_format=mixed

Server-id = 1

Datadir=/mysql/data

innodb_file_per_table = 1; Important useful

Sync_binlog = 1; This parameter is set for transaction security

SHOW GLOBAL VARIABLES like '%log '; View Global Settings

4. Add an authorized user from the library

# mysql-uroot-p

> GRANT REPLICATION SLAVE on * * to ' repluser ' @ ' 192 ... ' Identified by ' Replpass ';

> FLUSH privileges;

> SHOW MASTER status;

Note: If the primary server already has application data, the following processing is required for master-slave replication: (The primary database does a lock table operation and does not allow the data to be written again.)

Mysql> FLUSH TABLES with READ LOCK;

two. Install and configure from library

1. source installation cmake and MySQL see Main library installation steps

。 。 。 。 。 。

2. Modify from the Library configuration folder my.cnf

# VIM/ETC/MY.CNF

Relay-log = Relay-log

Relay-log-index = Relay-log.index

Service-id = 2

Binlog_format=mixed

Datadir=/mydata/data

innodb_file_per_table = 1

Read-only=yes/on/1/ture; Read-only permissions from the database

Note: Read-only does not take effect for users with Persu permissions

3. Connect to the main library

# Mysql-uroot

> Change MASTER to master_host= ' 192.168.x.x ', master_user= ' Repluser ',

Master_log_file= ' master-bin.00000x ', master_log_pos=xxx;

> START SLAVE

> SHOW SLAVE status\g;

STOP SLAVE Io_thread; Turn off threads individually

STOP SLAVE Sql_therad;

Two important files are stored under the/mydata/data/directory, Master.info/relay-log.info

These two files record the process of copying.

# file Master.info is an ASCII code file

Turn on the shutdown thread and it will be logged in the MySQL error log

Tail/mydata/data/node2.magedu.com.err

three. Mysql-proxy

1. Unzip the Mysql-proxy binary package, can be used directly

# Tar XF mysql-proxy-0.8.3-linux-rhel5-x86-32bit.tar.gz

# MV mysql-proxy-0.8.3 Mysql-proxy

# CD Mysql-proxy

# Vim Share/doc/mysql-proxy/rw-splitting.lua

If not Proxy.global.config.rwsplit then

Proxy.global.config.rwsplit = {

Min_idle_connections = 1,; Change parameter to 1

Max_idle_connections = 1,

Is_debug = False

}

End

2. Create a startup script

# Vim Mysql-proxy.sh

#!/bin/bash

/home/wang/mysql-proxy/bin/mysql-proxy--proxy-backend-addresses=192.168.10.48:3306-- proxy-read-only-backend-addresses=192.168.10.45:3306--proxy-lua-script=/home/wang/mysql-proxy/share/doc/ Mysql-proxy/rw-splitting.lua &

# sh mysql-proxy.sh

# NETSTAT-ANPT |grep 4040/4041; see if the process starts

3. on Another host through the MySQL command to connect to the Mysql-proxy 4040 port, by default, cannot connect, because the database is denied from remote login by default, so in the master and slave database are set permissions, allow remote login, it is important to note that In the master-slave do permissions, non-mysql-proxy this host, I am because of this mistake, pour half to not find the reason!!

1) Master and slave do the following configuration

# Mysql-uroot

> Grant all privileges on * * to ' wang ' @ '% ' identified by ' Wang ';

> Flush Privileges

>exit

2) can now successfully log on to the master-slave service on another host through the MySQL command, if the write operation will be on the primary server operation, read operation will be operating from the server, thus realizing the MySQL master-slave and read and write separation.

Four Connect MySQL server in Linux under Windows platform

Using Navicat for MySQL to connect to a remote MySQL server, but prompt: Can ' t get hostname for your address
Obviously already authorized with the grant command, it is necessary to perform one more step:

It may also be the cause of the firewall, close the iptables firewall, or see if SELinux is turned on, etc.;

1 ) Edit the/ETC/MY.CNF, in:
[Mysqld]
Add a line inside: skip-name-resolve

2MySQL ignores database table name capitalization
Modify the/etc/my.cnf, in
[Mysqld]
add a line inside: Lower_case_table_names=1


attached: MySQL Common error code description
1005: Failed to create TABLE
1006: Failed to create database
1007: Database already exists, failed to create database
1008: Database does not exist, database deletion failed
1009: Unable to delete database file causes database failure to be deleted
1010: Unable to delete data directory causes database failure to be deleted
1011: Failed to delete database file
1012: Cannot read records in system tables
1020: The record has been modified by another user
1021: Insufficient hard disk space, please increase hard disk free space
1022: Duplicate keyword, change record failed
1023: An error occurred while shutting down
1024x768: Read File Error
1025: An error occurred while changing the name
1026: Write file Error
1032: Record does not exist
1036: The data table is read-only and cannot be modified
1037: Insufficient system memory, please restart the database or restart the server
1038: Insufficient memory for sorting, increase sort buffer
1040: The maximum number of connections to the database has been reached, please increase the number of available connections to the database
1041: Insufficient system memory
1042: Invalid host name
1043: Invalid connection
1044: The current user does not have permission to access the database
1045: Unable to connect to database, user name or password error
1048: field cannot be empty
1049: Database does not exist
1050: Data table already exists
1051: Data table does not exist
1054: field does not exist
1065: Invalid SQL statement, SQL statement is empty
1081: Unable to establish socket connection
1114: The data sheet is full and cannot hold any records
1116: Too many Open data tables
1129: The database has an exception, please restart the database
1130: Failed to connect to database without permission to connect to database
1133: Database user does not exist
1141: The current user does not have permission to access the database
1142: The current user does not have permission to access the data table
1143: The current user does not have access to the fields in the datasheet
1146: Data table does not exist
1147: User access to the data table is undefined
1149: SQL statement syntax error
1158: Network error, read error, please check network connection status
1159: Network error, read timeout, check network connection status
1160: Network error, write error, please check network connection status
1161: Network error, write timeout, check network connection status
1062: Duplicate field value, failed to inbound
1169: Duplicate field value, update record failed
1177: Failed to open data table
1180: Commit TRANSACTION Failed
1181: ROLLBACK TRANSACTION failed
1203: The connection established by the current user and database has reached the maximum number of connections to the database, increase the number of available database connections or restart the database
1205: Gartha
1211: The current user does not have permission to create a user
1216: FOREIGN KEY constraint check failed, update child table record failed
1217: FOREIGN KEY constraint check failed, delete or modify master table record failed
1226: The current user is using more resources than allowed, please restart the database or restart the server
1227: Insufficient permissions, you do not have permission to do this
1235: MySQL version is too low to have this feature

Http://www.cnblogs.com/wangbaigui/p/4473522.html

MySQL master/slave configuration + read/write separation (RPM)