mysql-repository_fuer_ubuntu_einbinden
Inhaltsverzeichnis
MySQL-Repository für Ubuntu einbinden
Ubuntu 14.04 LTS (trusty)
Repository eintragen:
> vi /etc/apt/sources.list.d/mysql.list ### http://repo.mysql.com/apt/ubuntu/dists/trusty/ deb http://repo.mysql.com/apt/ubuntu/ trusty connector-python-2.1 deb http://repo.mysql.com/apt/ubuntu/ trusty mysql-5.7 deb http://repo.mysql.com/apt/ubuntu/ trusty mysql-apt-config deb http://repo.mysql.com/apt/ubuntu/ trusty mysql-tools deb http://repo.mysql.com/apt/ubuntu/ trusty mysql-utilities-1.5 deb http://repo.mysql.com/apt/ubuntu/ trusty router-2.0 deb http://repo.mysql.com/apt/ubuntu/ trusty workbench-6.3
GPG-Schlüssel von MySQL (Signature Checking Using GnuPG):
> apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 5072E1F5 > apt-key export 5072E1F5 > mysql_pubkey.asc > vi mysql_pubkey.asc
GPG-Schlüssel für das Repository importieren:
> gpg --import mysql_pubkey.asc > apt-key add mysql_pubkey.asc
oder
> gpg -a --export 5072E1F5 | apt-key add -
anzeigen lassen
> apt-key list > aptitude update
MySQL+MySQLFailsafe installieren:
> aptitude install mysql-community-server mysql-utilities
GTID-Replikation aktivieren bzw. Knoten verbinden
In diesem Beispiel soll der Replikations-User otto heißen.
Kürzfassung, wenn noch keine Daten in den Datenbanken, auf den Knoten, sind
[root@master ~]# echo "CREATE USER otto IDENTIFIED BY 'geheim'" | mysql [root@master ~]# echo "GRANT SUPER,REPLICATION CLIENT,RELOAD,REPLICATION SLAVE ON *.* TO 'otto'@'%';" | mysql [root@master ~]# mysqladmin reload [root@master ~]# echo "SHOW GLOBAL VARIABLES LIKE 'GTID_EXECUTED';" | mysql -t +---------------+--------------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------------+ | gtid_executed | cbca7510-08a2-11e6-a5e0-842b2b60d0af:1-135 | +---------------+--------------------------------------------+
[root@slave ~]# echo "STOP SLAVE; RESET MASTER; RESET SLAVE;" | mysql [root@slave ~]# echo "SET GLOBAL GTID_PURGED='cbca7510-08a2-11e6-a5e0-842b2b60d0af:1-135';" | mysql [root@slave ~]# echo "SHOW GLOBAL VARIABLES LIKE 'GTID_EXECUTED';"|mysql -t +---------------+--------------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------------+ | gtid_executed | cbca7510-08a2-11e6-a5e0-842b2b60d0af:1-135 | +---------------+--------------------------------------------+ [root@slave ~]# echo "STOP SLAVE; CHANGE MASTER TO MASTER_HOST='tcdb01', MASTER_PORT=3306, MASTER_USER='otto', MASTER_PASSWORD='geheim', MASTER_AUTO_POSITION=1, MASTER_SSL=0; START SLAVE;" | mysql [root@slave ~]# echo "SHOW SLAVE STATUS \G;" | mysql -t
[root@master ~]# echo "SHOW SLAVE HOSTS;" | mysql -t +-----------+-------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+-------+------+-----------+--------------------------------------+ | 9992 | slave | 3306 | 9991 | 8a4aff7c-08a2-11e6-98f0-842b2b4f25d0 | +-----------+-------+------+-----------+--------------------------------------+
MySQLFailover einrichten
Quelle: http://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqlfailover.html
In diesem Beispiel soll der Failover-User amidala heißen.
> echo "CREATE USER amidala IDENTIFIED BY 'passwort'" | mysql > echo "GRANT SELECT, SUPER, GRANT OPTION, REPLICATION SLAVE, RELOAD, DROP, CREATE, INSERT ON *.* TO 'amidala'@'%'" | mysql > echo "SELECT * FROM user WHERE user='amidala' \G;" | mysql -t mysql > mysqladmin reload [root@db01 ~]# mysqlfailover --master=root:passwort@db01:3306 --discover-slaves-login=root:passwort --candidates=root:passwort@db02:3306,root:passwort@db03:3306 --daemon=start MySQL Replication Failover Utility Failover Mode = auto Next Interval = Mon Apr 25 12:24:54 2016 Master Information ------------------ Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB mysql-bin.000002 816 GTID Executed Set cbca7510-08a2-11e6-a5e0-842b2b60d0af:1-138 Replication Health Status +-------+-------+---------+--------+------------+---------+ | host | port | role | state | gtid_mode | health | +-------+-------+---------+--------+------------+---------+ | db01 | 3306 | MASTER | UP | ON | OK | | db02 | 3306 | SLAVE | UP | ON | OK | | db03 | 3306 | SLAVE | UP | ON | OK | +-------+-------+---------+--------+------------+---------+
Failover starten
> mysqlfailover --master=root:passwort@db01:3306 --discover-slaves-login=root:passwort --candidates=root:passwort@db02:3306,root:passwort@db03:3306 --failover-mode=auto
Failover starting in 'auto' mode...
2016-04-25 16:25:35 PM CRITICAL The server db02:3306 does not comply to the latest GTID feature support. Errors:
Missing gtid_executed system variable.
ERROR: The server db02:3306 does not comply to the latest GTID feature support. Errors:
Missing gtid_executed system variable.
> mysqlrpladmin --slaves=root:passwort@db01:3306,root:passwort@db02:3306,root:passwort@db03:3306 --candidates=root:passwort@db02:3306,root:passwort@db03:3306 failover
WARNING: Using a password on the command line interface can be insecure.
# Checking privileges.
# Checking privileges on candidates.
# Performing failover.
ERROR: The server db02:3306 does not comply to the latest GTID feature support. Errors:
Missing gtid_executed system variable.
Bug (wurde in MySQL Utilities 1.6.3 behoben, also erst fehlerfrei in Ubuntu 16.04 LTS):
> vi /usr/lib/python2.7/dist-packages/mysql/utilities/common/server.py
...
1428 res = self.exec_query("SHOW GLOBAL VARIABLES LIKE 'gtid_executed'")
...
unter Verwendung der MySQL-Utilities wird eine Replikation nicht mit dem "CHANGE MASTER"-Kommando hergestellt, sondern mit dem "mysqlrpladmin"-Befehl:
[root@slave ~]# mysqlrpladmin --slaves=root:passwort@db02:3306,root:passwort@db03:3306 --candidates=root:passwort@db02:3306,root:passwort@db03:3306 failover WARNING: Using a password on the command line interface can be insecure. # Checking privileges. # Checking privileges on candidates. # Performing failover. # Candidate slave db02:3306 will become the new master. # Checking slaves status (before failover). # Preparing candidate for failover. # Creating replication user if it does not exist. # Stopping slaves. # Performing STOP on all slaves. # Switching slaves to new master. # Disconnecting new master as slave. # Starting slaves. # Performing START on all slaves. # Checking slaves for errors. # Failover complete. # # Replication Topology Health: +-------+-------+---------+--------+------------+---------+ | host | port | role | state | gtid_mode | health | +-------+-------+---------+--------+------------+---------+ | db02 | 3306 | MASTER | UP | ON | OK | | db03 | 3306 | SLAVE | UP | ON | OK | +-------+-------+---------+--------+------------+---------+ # ...done.
mit dem "mysqlfailover"-Befehl wird der Cluster überwacht und ggf. geschwenkt:
[root@master ~]# mysqlfailover --master=root:passwort@db01:3306 --discover-slaves-login=root:passwort --candidates=root:passwort@db01:3306,root:passwort@db03:3306 --failover-mode=auto MySQL Replication Failover Utility Failover Mode = auto Next Interval = Mon Apr 25 16:49:07 2016 Master Information ------------------ Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB mysql-bin.000003 444 GTID Executed Set 0d411222-0aed-11e6-81fb-842b2b60d0af:1-140 [...] Replication Health Status +-------+-------+---------+--------+------------+---------+ | host | port | role | state | gtid_mode | health | +-------+-------+---------+--------+------------+---------+ | db02 | 3306 | MASTER | UP | ON | OK | | db03 | 3306 | SLAVE | UP | ON | OK | +-------+-------+---------+--------+------------+---------+
Nach jedem Schwenk muss der "mysqlfailover"-Befehl aber mit den geänderten Einstellungen neu aufgerufen werden!
/home/http/wiki/data/pages/mysql-repository_fuer_ubuntu_einbinden.txt · Zuletzt geändert: von manfred