Bug 1247077 - [origin_devexp_625]Cannot sync user and db info to slave host for mysql
[origin_devexp_625]Cannot sync user and db info to slave host for mysql
Status: CLOSED CURRENTRELEASE
Product: OpenShift Origin
Classification: Red Hat
Component: Image (Show other bugs)
3.x
Unspecified Unspecified
medium Severity medium
: ---
: ---
Assigned To: Martin Nagy
Wenjing Zheng
: UpcomingRelease
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2015-07-27 05:43 EDT by Wenjing Zheng
Modified: 2016-07-26 19:47 EDT (History)
6 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2015-11-23 16:18:18 EST
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)

  None (edit)
Description Wenjing Zheng 2015-07-27 05:43:33 EDT
Description:
Cannot sync user info to slave host:
In master pod:
mysql> select Host,User,Password from mysql.user;
+----------------------+--------+-------------------------------------------+
| Host                 | User   | Password                                  |
+----------------------+--------+-------------------------------------------+
| localhost            | root   |                                           |
| mysql-master-1-qivnc | root   |                                           |
| 127.0.0.1            | root   |                                           |
| ::1                  | root   |                                           |
| localhost            |        |                                           |
| mysql-master-1-qivnc |        |                                           |
| %                    | user   | *66E3DAE754277F252FDB7AC9FB33C2FE9EDB3508 |
| %                    | root   | *1D121239D0413B33D3892736D8667BDF5D24D392 |
| %                    | master | *7602EA0779EE45AA1F9807B8A5952DBCC739A1FA |
+----------------------+--------+-------------------------------------------+
 
In slave pod:
mysql> select Host,User,Password from mysql.user;
+---------------------+------+----------+
| Host                | User | Password |
+---------------------+------+----------+
| localhost           | root |          |
| mysql-slave-1-518yw | root |          |
| 127.0.0.1           | root |          |
| ::1                 | root |          |
| localhost           |      |          |
| mysql-slave-1-518yw |      |          |
+---------------------+------+----------+

Version-Release number of selected component (if applicable):
openshift v3.0.1.0-503-g7cc6deb
kubernetes v1.0.0

How reproducible:
always

steps to Reproduce:
1. Create Persistentvolume as cluster-admin with below file:
$cat pv.json
apiVersion: v1
kind: PersistentVolume
metadata:
  name: pv0001
spec:
  capacity:
    storage: 5Gi
  accessModes:
    - ReadWriteOnce
  nfs:
    path: /nfsshare
    server: 10.66.79.155
$oc create -f pv.json
2. Create a new project
3.  Process and create resources with below json file
$oc process -f https://raw.githubusercontent.com/openshift/mysql/master/5.5/examples/replica/mysql_replica.json | oc create -f -
4.  Check the start-up pod status
5. In master pod:
mysql> select Host,User,Password from mysql.user;
6. In slave pod:
mysql> select Host,User,Password from mysql.user;

Actual results:
The user info is not synced to slave host between step 5 and step6

Expected results:
It should sync to slave host

Additional info:
In multi-node env, even salve host cannot be seen like below; it can be seen with fedora instance although user info failed to sync either:
mysql> show slave hosts;
+------------+-------------+------+------------+
| Server_id  | Host        | Port | Master_id  |
+------------+-------------+------+------------+
| 2248500234 | 172.17.0.52 | 3306 | 3192619690 |
+------------+-------------+------+------------+
1 row in set (0.00 sec)
Comment 1 Martin Nagy 2015-07-27 10:24:47 EDT
Seems like the problem is that we replicate the created DB exclusively. From MySQL configuration file:
binlog_do_db  = ${MYSQL_DATABASE}
If I understand the documentation correctly, this excludes all other databases. This will be problematic also if the user decides to use the admin account to create a new DB.

The obvious fix will probably be to remove the offending line.
Comment 3 DeShuai Ma 2015-08-04 06:13:30 EDT
still can't sync userdb to slave.The db "userdb" exist on master, but can't find it on slave.

1.On slave there is no db "userdb"
[fedora@ip-10-150-81-42 mysql]$ oc rsh mysql-slave-3-vcwh1 -n dma1
bash-4.2$ mysql -u root
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      107 | userdb       |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)


2. On master the db "userdb" exist
[fedora@ip-10-150-81-42 mysql]$ oc rsh mysql-master-3-vhplg -n dma1
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      401 | userdb       |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> show slave hosts;
+------------+-------------+------+------------+
| Server_id  | Host        | Port | Master_id  |
+------------+-------------+------+------------+
| 1183187913 | 172.17.0.68 | 3306 | 3769513643 |
+------------+-------------+------+------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| userdb             |
+--------------------+
4 rows in set (0.01 sec)

mysql> exit
Comment 4 Martin Nagy 2015-09-01 07:23:23 EDT
We should now be syncing the user info database.
Comment 5 XiuJuan Wang 2015-09-01 23:18:15 EDT
This bug is blocked to verify by bug https://bugzilla.redhat.com/show_bug.cgi?id=1258423.
If add pv to pod, the mysql-master can't be running, will hold to verify this bug.
# oc get  pods 
NAME                   READY     STATUS                                                              RESTARTS   AGE
mysql-master-1-dc1g0   0/1       Image: openshift/mysql-55-centos7 is ready, container is creating   0          4m
mysql-slave-1-6fo83    1/1       Running                                                             0          5m
Comment 6 Wenjing Zheng 2015-09-07 03:02:39 EDT
Verified on below version:
openshift v1.0.5-264-g11321c4
kubernetes v1.1.0-alpha.0-1605-g44c91b1
In slave pod:
mysql> select Host,User,Password from mysql.user;
+----------------------+--------+-------------------------------------------+
| Host                 | User   | Password                                  |
+----------------------+--------+-------------------------------------------+
| localhost            | root   |                                           |
| mysql-master-1-ikrg3 | root   |                                           |
| 127.0.0.1            | root   |                                           |
| ::1                  | root   |                                           |
| localhost            |        |                                           |
| mysql-master-1-ikrg3 |        |                                           |
| %                    | user   | *1D187AFA5015ED7C5BB740D36D5DF0C367FF3872 |
| %                    | root   | *C4755A3CB3D1E519C5841771C7CD7BEB8A96AE24 |
| %                    | master | *EC00D5C7300586463CDE5EB2DD9B2D0F4D7A1467 |
+----------------------+--------+-------------------------------------------+
9 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| replication        |
| userdb             |
+--------------------+
5 rows in set (0.00 sec)

Note You need to log in before you can comment on or make changes to this bug.