MYSQL-replication（AB复制）安装步骤无错版

Script started on Wed 16 Nov 2011 05:08:26 PM CST

username@username-PC:~$ scp Desktop/mysql-5.1.59.tar.gz root@192.168.56.101:/root

username@username-PC:~$ ssh root@192.168.56.101

root@192.168.56.101's password:

Last login: Wed Nov 16 02:42:15 2011 from 192.168.56.1

[root@mysqla ~]# ls

anaconda-ks.cfg Desktop install.log install.log.syslog mysql-5.1.59.tar.gz

[root@mysqla ~]# tar xf mysql-5.1.59.tar.gz

[root@mysqla ~]# cd mysql-5.1.59

[root@mysqla mysql-5.1.59]# useradd mysql -s /sbin/nologin

[root@mysqla mysql-5.1.59]# mkdir -p /data/mysql_db

[root@mysqla mysql-5.1.59]# ./configure prefix=/usr/local/mysql localstatedir=/data/mysql_db --with-extra-charsets=utf8,gb2312,gbk –with—pthread enable-thread-safe-client && make && make install

[root@mysqla local]# cd /root/mysql-5.1.59

[root@mysqla mysql-5.1.59]# cp support-files/my-large.cnf /etc/my.cnf

[root@mysqla ~]# cd /usr/local

[root@mysqla local]# ll | grep mysql

drwxr-xr-x 10 root root 4096 Nov 16 04:01 mysql

[root@mysqla mysql]# chown mysql: . --R

[root@mysqla mysql]# cd /data/

[root@mysqla data]# cd mysql_db/

[root@mysqla mysql_db]# ls

mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.index test

[root@mysqla mysql_db]# more /etc/my.cnf | sed -n '/^#/!p'

[root@mysqla ~]# /usr/local/mysql/bin/mysqld_safe --user=mysql&

[1] 2584

mysqld_safe Logging to '/data/mysql_db/mysqla.err'.

mysqld_safe Starting mysqld daemon with databases from /data/mysql_db

[root@mysqla ~]# ps -ef | grep mysql

[root@mysqla ~]# ps auxww |grep mysqld | grep -v grep

//”root” is father process; “mysql” is son process

[root@mysqla ~]# pstree | grep mysqld | grep -v grep

| |-mysqld_safe---mysqld---{mysqld}

[root@mysqla ~]# vi /etc/my.cnf

[client]

default-character-set=utf8

[mysqld]

default-character-set=utf8

init_connect='SET NAMES utf8'

max_connections=1000

ft_min_word_len=1

expire_logs_days=7

[root@mysqlan~]#netstat.-anp | grep33306

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2695/mysqld

[root@mysqla ~]# ls /tmp/mysql.sock

/tmp/mysql.sock

[root@mysqla mysql]# /usr/local/mysql/bin/mysql

[root@mysqla ~]# vi .bash_profile

//add PATH=$HOME:/usr/local/mysql/bin

[root@mysqla ~]# source .bash_profile

[root@mysqla ~]# mysql

//go into mysql

[root@mysqla ~]# mysqladmin shutdown

mysqld_safe mysqld from pid file /data/mysql_db/mysqla.pid ended

[1]+ Done /usr/local/mysql/bin/mysqld_safe -user=mysql

//shutdown mysql

[root@mysqla ~]# ps -ef |grep mysqld

//check mysqld process

[root@mysqla ~]# mysql_safe --user=mysql &

[1] 2540

mysqld_safe Logging to '/data/mysql_db/mysqla.err'.

mysqld_safe Starting mysqld daemon with databases from /data/mysql_db

//start mysql

[root@mysqla ~]#

[root@mysqla ~]# ps -ef |grep mysqld

root 2540 2511 0 15:24 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --user=mysql

mysql 2645 2540 0 15:24 pts/0 00:00:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql_db --user=mysql --log-error=/data/mysql_db/mysqla.err --pid-file=/data/mysql_db/mysqla.pid --socket=/tmp/mysql.sock --port=3306

root 2649 2511 0 15:24 pts/0 00:00:00 grep mysqld

+++++++++++++++++++++++++++++++++++++++++

//go into mysql and grant permission

//and will set replication “mysql-A”

[root@mysqla ~]# mysql

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000005 | 260 | | |

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

1 row in set (0.00 sec)

mysql>GRANT REPLICATION SLAVE ON *.* TO test@192.168.56.103@identified by '123456';

Query OK, 0 rows affected (0.01 sec)

mysql> exit

Bye

++++++++++++++++++++++++++++++++++

// setting replication “mysql-B”

[root@mysqlb ~]# vi /etc/my.cnf

server-id= 10 //don't set it the same as master's server-id and larger than it

[root@mysqlb ~]# /usr/local/mysql/bin/mysqld_safe --user=mysqld&

[1] 2642

mysqld_safe Logging to '/data/mysql_db/mysqlb.err'.

mysqld_safe Starting mysqld daemon with databases from /data/mysql_db

[root@mysqlb ~]# mysql -u root -p

Enter password:

// setting slave info to access master server

mysql> change master to master_host='192.168.56.101',

-> master_user='test',

-> master_password='123456',

-> master_log_file='mysql-bin.000005',

-> master_log_pos=26

Query OK, 0 rows affected (0.01 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

*************************** 1. row ***************************

// must two “YES”,else replication fault

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

mysql> show slave status;

++++++++++++++++++++++++++++++++++++++

//back to master and insert database,table to it.

[root@mysqla ~]# mysql

mysql> CREATE DATABASE first_tb;

Query OK, 1 row affected (0.01 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| first_tb |

| mysql |

| test |

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

4 rows in set (0.02 sec)

mysql> use irst_tb;

Database changed

mysql> CREATE TABLE first_table(id)int(3),)name)char(10));

Query OK, 0 rows affected (0.00 sec)

mysql> insert into first_table values (001test');

Query OK, 1 row affected (0.00 sec)

mysql> exit

Bye

// go to slave server

[root@mysqlb ~]# mysql

mysql> show databases;

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

| Database |

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

| information_schema |

| first_tb |

| mysql |

| test |

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

4 rows in set (0.02 sec)

mysql> use first_tb;

Database changed

mysql> show tables;

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

| Tables_in_first_tb |

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

| first_table |

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

1 row in set (0.00 sec)

mysql> select * from first_table;

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

| id | name |

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

| 1 | test |

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

1 row in set (0.00 sec)

mysql> exit

Bye