mysql主从复制，server 或 Docker容器

mysql set up replication refer to Mysql doc

On master check if logging binary enabled:
show variables like 'log_bin', refer to how to know if mysql binary log is enable through sql command?

1.Config my.cnf
Master:

	[mysqld]
	log-bin=mysql-bin
	server-id=1

Slave:

[mysqld]
server-id=2

2. Create user and grant privileges for replication: (On Master create user, which will be used by the slave)
For example, to set up a new user, repl, that can connect for replication from any host within the example.com domain, issue these statements on the master:

mysql> CREATE USER 'repl'@'%.example.com' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.example.com';

If needed run FLUSH PRIVILEGES; to load the pasword table after update or grant

3. Obtaining the replication master binary log coordinates(On Master)
Start a session and lock the mysql with read lock,
mysql> FLUSH TABLES WITH READ LOCK;
In another session,get current binary log file name and position

mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73       | test         | manual,mysql     |
+------------------+----------+--------------+------------------+

4. Creating a data snapshot using mysqldump(On Master)
shell> mysqldump -u root -p --all-databases --master-data > dbdump.db or shell> mysqldump -u root -p database_name --master-data >database_name.db

note: –master-data doc, when use --master-data=2

cat backup.db | more
--
-- Host: localhost    Database: xxxxx
-- ------------------------------------------------------
-- Server version       5.5.60-MariaD

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000011', MASTER_LOG_POS=107962617;

5. Release the lock(On Master)
mysql> UNLOCK TABLES;

6. Load db(On Slave)
shell> mysql -u root -p < dbdump.db
or
shell> mysql -u root -p database_name < database_name.db (here maybe need create database_name first)
then start slave mysql> start slave
Here maybe get this error ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO
Setting the master Configuration on the slave to solve it.

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='master_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;

If you need to restart mysql:/etc/init.d/mysql restart or /etc/init.d/mariadb restart

7. If you want to run the replication in docker container, refer to mysql-replication for DOCKER mysql master and slave.

8. You also can just run a mysql slave in docker

9. install and config docker
install docker

sudo yum install -y yum-utils device-mapper-persistent-data  lvm2
sudo yum-config-manager  --add-repo https://download.docker.com/linux/centos/docker-ce.repo
sudo yum install docker-ce

uninstall docker sudo yum remove docker-ce
start docker sudo systemctl start docker
auto start docker when os on sudo systemctl enable docker
auto start container docker run -dit --restart unless-stopped redis, refer to start containers automatically
restart container docker restart containername
check running container docker ps
check images docker images
issues:

	Warning: failed to get default registry endpoint from daemon (Cannot connect to the Docker daemon at unix:///var/run/docker.sock. Is the docker daemon running?)
	Solution: `su && service docker start && docker images;`
	
	Warning: failed to get default registry endpoint from daemon (Got permission denied while trying to connect to the Docker daemon socket at unix:///var/run/docker.sock: Get http://%2Fvar%2Frun%2Fdocker.sock/v1.38/info: dial unix /var/run/docker.sock: connect: permission denied)
	Solution: run with `sudo`

10. start mysql slave in docker container
pull image docker pull mysql:5.5 refer to docker mysql
run container from docker run --name mysql-slave -e MYSQL_ROOT_PASSWORD=password -d mysql:5.5

copy the dump db to container docker cp database_name.db container:/root
loginto the container docker exec -it mysql_slaves /bin/bash
then config mysql as above.
You may be need to install vim, to edit the my.cnf with apt update and apt install vim

11. check slave status

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.186
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000051
          Read_Master_Log_Pos: 245
               Relay_Log_File: 80b47762156a-relay-bin.000015
                Relay_Log_Pos: 391
        Relay_Master_Log_File: mysql-bin.000051
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 245
              Relay_Log_Space: 554
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2
1 row in set (0.00 sec)