Mysql Slave server setup step by step

Mysql Slave server setup step

Mysql 备用数据库部署步骤

• 下载及校验（5.5）
  从官方下载http://www.mysql.com/downloads/mysql/#downloads页面选择对应稳定版本后下载：
  http://dev.mysql.com/downloads/mysql/#downloads  
  (选择源码版Generic Linux (Architecture Independent), Compressed TAR Archive)

#wget   

#md5sum [filename]                         ；对比下载页面的md5值

*note：

1. 也可从其他服务器复制
2. 如果系统已经默认安装了低版本的，可以先用命令yum erase mysql 删除

• 安装依赖组件bison和cmake

bison: yum install bison

cmake： yum install cmake ncurses-devel

note：5.5以后，cmake取代configure编译

• Preconfiguration setup

#groupadd mysql

#useradd -r -g mysql mysql

note: mysql服务将会以mysql启动而不是root,这样可减少安全风险,如果存在则会报已存在

#mkdir -p /opt/mysqldata/{data,logbin}

# chown -R mysql:mysql /opt/mysqldata/data

• Beginning of source-build specific instructions

#tar zxvf mysql-VERSION.tar.gz

#cd mysql-VERSIONc

#cmake . \

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/opt/mysqldata/data -DSYSCONFDIR=/etc/ \

-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \

-DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all \

-DWITH_MYISAM_STORAGE_ENGINE=1 \

-DWITH_SPHINX_STORAGE_ENGINE=1 \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_MEMORY_STORAGE_ENGINE=1 \

-DWITH_READLINE=1 \

-DENABLED_LOCAL_INFILE=1 \

-DMYSQL_USER=mysql

note:

1. 错误”Curses library not found. Please install appropriate package,“　
   需 yum install ncurses-devel 　　；然后重新编译
2. 重新编译则需# make clean # rm -f CMakeCache.txt
3. cmake报错则是缺失相关组件，gcc/c++/make/autoconf /automake 等：
   *yum install gcc* c++ autoconf automake zlib* libxml* ncurses-devel libmcrypt* libtool-ltdl-devel*
   然后删除原解压目录（或CMakeCache.txt文件），重新解压后再编译。
4. 参数说明：
   -DWITH_READLINE=1 　       　——readline 库
   -DENABLED_LOCAL_INFILE=1 　——允许从本地导入数据
5. cmake参数说明
6. 可忽略：
   警告：提领类型双关的指针将破坏强重叠规则,  
   警告：类型双关作用在不完全类型上可能破坏强重叠规则

#make

#make install

• Post installation setup

# cd /usr/local/mysql

# chown -R mysql:mysql .

# chown -R mysql /opt/mysqldata/

# cp ./support-files/my-huge.cnf /etc/my.cnf　　　　 //复制配置文件
　or　＃cp ./support-files/my-innodb-heavy-4G.cnf /etc/my.cnf　

#vi /etc/my.cnf

在 [mysqld] 段增加

basedir=/usr/local/mysql

datadir=/opt/mysqldata/data

其他配置参考后文：配置优化。
注意以下值的修改　(例子4G内存)：

skip-name-resolve

character-set-server = utf8

slow_query_log_file= ——不同于5.5之前版本

innodb_buffer_pool_size = 2G　

innodb_flush_log_at_trx_commit = 2

innodb_data_file_path = ibdata1:3G:autoextend

innodb_lock_wait_timeout = 120

innodb_flush_method = O_DIRECT

• 初始化数据：

# /usr/local/mysql/scripts/mysql_install_db \--defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/opt/mysqldata/data --user=mysql

# chown -R root /usr/local/mysql

# cd /usr/local/bin

# ln -fs /usr/local/mysql/bin/mysql mysql

# ln -s /usr/local/mysql/lib/libmysqlclient.so /usr/lib/libmysqlclient.so

64位系统：ln -s /usr/local/mysql/lib/libmysqlclient.so.18  /usr/lib64/

【optional】#bin/mysqld_safe --user=mysql &   //测试，用netstat -tnl查看一下3306端口

• 设置服务为开机自启动

# cd /usr/local/mysql/

# cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld

# vi /etc/rc.d/init.d/mysqld ,编辑

basedir=/usr/local/mysql

datadir=/opt/mysqldata/data

# chmod 755 /etc/rc.d/init.d/mysqld

# chkconfig --add mysqld

# chkconfig --level 345 mysqld on

• 添加myql/bin到path变量

＃ vi /etc/profile
　在”Path manipulation“　下添加：
       pathmunge /usr/local/mysql/bin/

然后# source /etc/profile

• 服务启动、停止

#service mysqld start

#service mysqld stop

• 更换root@localhost的密码

更改root密码：

• shell 下：
  # mysqladmin -u root password 'newpassword'

• mysql下
  --for 当前用户：
  SET PASSWORD=PASSWORD('password')

   
--for特定用户:

SET PASSWORD FOR ‘user_name’@’host_name’=PASSWORD('newpass');

其相当于：

mysql> UPDATE user SET Password=PASSWORD(’new_password’) WHERE user=’username’ and HOST=’hostname’

mysql> FLUSH PRIVILEGES；

• Reference for recover root password

http://www.cyberciti.biz/tips/recover-mysql-root-password.html

• Create User in mysql 创造远程用户

比如说要创造以下的用户

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

| user | host   |

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

| apiroot | %       |

| mwroot  | %       |

| oaroot  | %       |

| ocroot  | %       |

| slave   | %       |

| mbakup  | localhost   |

| root | localhost         |

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

Create User in mysql

mysql> grant usage on *.* to  'apiroot'@'%' IDENTIFIED BY 'password'

mysql> grant usage on *.* to 'mwroot'@'%' IDENTIFIED BY 'password'

mysql> grant usage on *.* to 'oaroot'@'%' IDENTIFIED BY 'password';

mysql> grant usage on *.* to 'ocroot'@'%' IDENTIFIED BY 'password';

mysql> grant replication slave on *.* to 'slave'@'%' IDENTIFIED BY 'password' ;

mysql> grant SELECT, Lock tables, Reload,SUPER,REPLICATION CLIENT on *.* to 'mbakup'@'localhost' IDENTIFIED BY 'password' ;

Note:

grant usage=means we create this user, but this user doesn't have any privilege.

grant all privilege= means have all the privilege.

grant replication slave=  means this user have slave replication privilege and other privilege are not.

Those all privilege can be check in mysql with below command.

• First choose your databases

mysql> use mysql;

• Second check your user is it are successful create.

mysql> select user,host from mysql.user;

• Third check the privilege of all the user that was created just now.

mysql> select * from mysql.user\G

• Fourth, create related databases

mysql>create database mingmen(DBname);

• Fifth ,log in into master mysqld server /bak/dbdump/copy the latest date of the .dmp.

# cd /bak/dbdump/.dmp

• We can from the slave server, means the server want to become slave, use sftp login into master mysql server, then use (get databases.dmp)

• After done all those upper step, then at slave server find the .dmp file. use below cmd to import the .dmp to database.

# mysql -u root -p databases name <.dmp file.

then keep waiting.do not cancel..it take sometime. After done, then it is time to do replication.

Replication of MySQL

Principle

通过使用mysql的bin log，使从数据库执行同主库一样的更新操作，来保证主从数据的一致

• In master:

 1. 创建同步账户（注意仅需要赋予replication权限）Create user for replication and grant this account privilege required for replication:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' IDENTIFIED BY 'slavepassword';  

this step normally are no need to do,if u are doing a slave server.

 2. 开启binary log。By default, binary logging is enabled.

  To make sure binary logging is enabled in the master, check if this option exist in /etc/my.cnf

               log-bin=mysql-bin

  3. Tip: It is highly advised to specify a descriptive name for the binary logs.  Rename log-bin option value to specify local hostname.

             * Example:

             # service mysqld stop

             # vi /etc/my.cnf

              log-bin=/opt/mysqldata/logbin/snL02-mysql2

             # service mysqld start

4. 配置server id等

4a) In /etc/my.cnf, make sure mysql server on the master has a unique server-id that differs from the master and any of the slaves. It can be set to 1 up to (2^32)-1 which is roughly up to 4294967295.  

For this example, will just use 1. For actual use, last path of IP address is recommended: 10.1.1.155

        # vi /etc/my.cnf

Edit line: server-id       =  155    noramlly server id will be follow by server last ip.

       

For InnoDb tables durability and consistency in replication setup, take note to have these options in /etc/my.cnf

  innodb_flush_log_at_trx_commit = 1

sync_binlog=1 (not in use)

innodb_support_xa=1  

Note: Disable this option to reduce number of disk flushes and improve InnoDB performance. (not in use)

 

#skip-networking

Note: Ensure  -networking option in /etc/my.cnf is not enabled

4b) Tip: For InnoDB storage engine below is preferable way to create data snapshot:

•   Create dump of specific database you want to replicate:

            # mysqldump -u root -p --single-transaction --master-data --databases  xxx>                 XXXdump.sql.2011

 4c) Create a data snapshot of specific database.

•  For any storage engine:

• For MyISAM only. Close all open tables and locks all tables of all databases with a read lock.  Leave this first session open so the read lock remains in effect.

 * mysql> FLUSH TABLES WITH READ LOCK;

• Open another second session and determine/record current binary log file name  and position

             mysql> SHOW MASTER STATUS \G

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

                                  File: mysql-bin.000021

                              Position: 106

                          Binlog_Do_DB:

                      Binlog_Ignore_DB:

                      1 row in set (0.00 sec)

• Create dump of specific database you want to replicate:(不导数据)

              * # mysqldump -u root -p --opt --databases innodb > innodbump.sql

• Return to first session then release lock:

                           * mysql> UNLOCK TABLES;

In Slave:

• 配置从库Stop mysql server on the slave and set unique server-id in /etc/my.cnf. It can be set to 2 up to (2^32)-1 which is roughly up to 4294967295. For this example will use 2.

       # service mysqld stop

       # vi /etc/my.cnf

       Edit line:

server-id = 1 to

             server-id = 2

• Add another option:

skip-slave-start

• Configure the slave with the replication coordinates of the master by adding the following options in /etc/my.cnf.

##master-host=                                                     

##master-port=3306

##master-user=

##master-password=

note:## not to means comment out this 4 line.

• this is cancel the slave safely  without restart the slave mysql 不重起情况下进行主从切换

replicate-do-table=db_name.tbl_name

log-slave-updates    

replicate-wild-do-table = dbname.%

note:/etc/my.cnf can be take a look on other server.

• after all these setting, restart mysql.

#service mysqld start.

• Then in master mysql server log in to mysql the n check the log_pos number and master_log_file.

mysql>show master status \G;

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

        File: mSDB154.000011      <<<<

    Position: 107      <<<<<

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

• Then in slave server mysql console

change master to master_host='112.121.178.122',master_port=3306,master_user='slave',master_password='password',master_log_file='hostname.000011',master_log_pos=107;

-master host means master mysql ip

-master port means master mysql port

-master user means master mysql user

-master password should be the user password.

• Start the slave thread:

* mysql> START SLAVE;

• After successfull replication, you'll find two new files in your mysql data directory. These are master.info and relay-log.info which contains the master's replication coordinates and keep track of how much of the master's binary log have been executed respectively.

• Check status information of slave threads and take note of Master_Log_File, Exec_Master_Log_Pos, Read_Master_Log_Pos values, and Seconds_Behind_Master:

* mysql> SHOW SLAVE STATUS \G

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

                         Slave_IO_State: Waiting for master to send event

                            

                  

In Master:

• Compare recorded values to status information of master's binary log files.  Values of File should match with Master_Log_File and Position should match Exec_Master_Log_Pos.  If the position does not match, make sure that Position is at least match with Read_Master_Log_PosS and wait for the slave to catch up with the master.  After some time Seconds_Behind_Master will be 0.

* mysql> SHOW MASTER STATUS \G

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

                。。。

In Slave:

•  Comment skip-slave-start option in /etc/my.cnf

       # service mysqld stop

       # vi /etc/my.cnf

              * #skip-slave-start               <-------- comment this

       # service mysqld start

Happy mysqling!!!!

sftp cmd

sftp -oPort=22122 root@IP