MySQL synchronous replication

1, installation CMake

Tar zxvf cmake-2.8.11.2.tar.gz

CD cmake-2.8.11.2

./configure

Make && make install

2. Install MySQL

Groupadd MySQL

useradd-g MySQL MySQL

Tar zxvf mysql-5.5.11.tar.gz

CD mysql-5.5.11

CMake. -dcmake_install_prefix=/usr/local/mysql/-dmysql_datadir=/usr/local/mysql/data-dwith_innobase_storage_engine=1- Dmysql_tcp_port=3306-dextra_charsets=all-ddefault_charset=utf8-ddefault_collation=utf8_general_ci-dmysql_unix_ Addr=/usr/local/mysql/data/mysql.sock-dmysql_user=mysql-dwith_debug=0

Make

Make install

3. Copying configuration files

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

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

chmod 755/etc/init.d/mysqld

Add to boot Boot

Chkconfig--add mysqld

Chkconfig--level 2345 mysqld on

Chkconfig--list mysqld

4. Add Environment variables

Vi/root/.bashrc

Path=/usr/local/mysql/bin:${path}

Export PATH

5. Change the root password

Mysql>use MySQL;

Mysql>update User Set Password=password ("123456") where user= ' root ';

Mysql>flush privileges;

6. Initialize the database

chmod 755/usr/local/src/mysql/scripts/mysql_install_db

./scripts/mysql_install_db--user=mysql--basedir=/usr/local/mysql--datadir=/usr/local/mysql/data

7. Modify the permissions in MySQL data directory

Chown-r Mysql.mysql Data

Service mysqld Start

8. Import and export of data

Import: MySQL--default-character-set=utf8 MAIN < main.sql

Export: mysqldump--default-character-set=utf8 MAIN > Main.sql

9. Add slave user to master library and set password and access rights

Mysql>grant REPLICATION SLAVE On * * to [e-mail protected] "%" identified by ' 123456 ';

10, modify the Slave library my.cnf file, add Server-id = slave host number, restart Mysqld

11, show master status; see if Master is OK, find file and position

+------------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+------------------+----------+--------------+------------------+

|      mysql-bin.000006 |              1051 |                  | |

+------------------+----------+--------------+------------------+

12. Execute on Slave library

Mysql>change Master to master_host= ' master IP address ', master_user= ' slave ', master_password= ' slave ', master_log_file= ' Master File ', Master_log_pos=master Position;

Mysql>flush privileges;

13, slave start

Show slave status

14. Test synchronous replication

Create a new table in master any library creating table test (ename varchar (10)); see if this table is synchronized in slave.

15. Remote BACKUP Database

Open user access permissions on the database machine, such as slave users

Mysql>grant all on * * to [e-mail protected] "%" identified by ' slave ';

Performing on a native environment

Mysqldump-uroot-p-h10.28.10.151 mysql > Mysql.sql

Problem: If you do not succeed when you start MySQL, it is generally the permissions of the data directory and the size of the memory allocation in the MY.CNF configuration file.

MySQL slave error_code:1062

For versions older than 5.1, use the

Slave stop;

Set global sql_slave_skip_counter=n; \ \ n= positive integers, see how many mistakes you have, skip a few

Slave start;

For more than 5.1 versions, there is no problem with the variable settings, it seems to be more user-friendly than the previous version processing, do not skip.

SET GLOBAL slave_exec_mode = ' idempotent ';

This article is from the "Kimileonis" blog, make sure to keep this source http://kimileonis.blog.51cto.com/5531747/1538418