====== Replikacja MariaDB ====== [będzie aktualizowane w miarę postępu prac] ==== Najprostsza replikacja ==== Dwa serwery: * maria1.ping.local - primary, * maria2.ping.local - replika, tylko do odczytu. == Konfiguracja maria1 == Sekcja [mariadb] z pliku /etc/my.cnf.d/mariadb-server.cnf # This group is only read by MariaDB servers, not by MySQL. # If you use the same .cnf file for MySQL and MariaDB, # you can put MariaDB-only options here [mariadb] log-bin server_id=1 log-basename=master1 binlog-format=mixed Należy zrestartować MariaDB, żeby zaczytała zmiany. W shellu mysql: MariaDB [(none)]> CREATE USER 'replication_user'@'%' IDENTIFIED BY 'qwerty123'; MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%'; MariaDB [(none)]> FLUSH TABLES WITH READ LOCK; MariaDB [(none)]> show master status; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | master1-bin.000004 | 344 | | | +--------------------+----------+--------------+------------------+ 1 row in set (0.001 sec) Nazwa użytkownika oraz jego hasło może być inne. Należy wyeksportować wszystkie bazy danych do pliku: # mysqldump --all-databases > /home/lukasz/bazy_danych.sql Plik należy pobrać (na przykład programem scp) oraz wysłać na serwer maria2. == Konfiguracja maria2 == Sekcja [mariadb] z pliku /etc/my.cnf.d/mariadb-server.cnf # This group is only read by MariaDB servers, not by MySQL. # If you use the same .cnf file for MySQL and MariaDB, # you can put MariaDB-only options here [mariadb] log-bin server_id=2 #read_only=1 # potem odkomentowac Należy zrestartować MariaDB, żeby zaczytała zmiany. Następnie trzeba zaimportować bazy danych z serwera maria1: mysql < /home/lukasz/bazy_danych.sql Potem w shellu mysql: change master to master_host='maria1.ping.local', master_user='replication_user', master_password='qwerty123', master_port=3306, master_log_file='master1-bin.000004', master_log_pos=334, master_connect_retry=10; Następnie należy odkomentować lnijkę //#read_only=1// w pliku mariadb-server.conf, dzięki czemu użytkownicy bez uprawnień SUPER nie będą mogli modyfikować bazy danych. Po zamianie pliku należy zrestartować bazę danych. == Sprzątanie i uruchomienie replikacji == W shellu bazy danych na serwerze maria1 można już zdjąć blokady: MariaDB [(none)]> unlock tables; W shellu bazy danych na serwerze maria2 upewnić się, że replikacja przebiega pomyślnie: MariaDB [(none)]> start slave; MariaDB [(none)]> show slave status; +----------------------------------+-------------------+------------------+-------------+---------------+--------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+----------------+--------------------+------------+-------------+-------------------------+-----------------------------+---------------+-----------+---------------------+--------------------------------------------------------+------------------+--------------------------------+----------------------------+ | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_SSL_Crl | Master_SSL_Crlpath | Using_Gtid | Gtid_IO_Pos | Replicate_Do_Domain_Ids | Replicate_Ignore_Domain_Ids | Parallel_Mode | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Slave_DDL_Groups | Slave_Non_Transactional_Groups | Slave_Transactional_Groups | +----------------------------------+-------------------+------------------+-------------+---------------+--------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+----------------+--------------------+------------+-------------+-------------------------+-----------------------------+---------------+-----------+---------------------+--------------------------------------------------------+------------------+--------------------------------+----------------------------+ | Waiting for master to send event | maria1.ping.local | replication_user | 3306 | 10 | master1-bin.000004 | 344 | mariadb-relay-bin.000012 | 645 | master1-bin.000004 | Yes | Yes | | | | | | | 0 | | 0 | 344 | 1257 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 1 | | | No | | | | optimistic | 0 | NULL | Slave has read all relay log; waiting for more updates | 0 | 0 | 0 | +----------------------------------+-------------------+------------------+-------------+---------------+--------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+----------------+--------------------+------------+-------------+-------------------------+-----------------------------+---------------+-----------+---------------------+--------------------------------------------------------+------------------+--------------------------------+----------------------------+ 1 row in set (0.000 sec) Można dla pewności założyć nową bazę danych na serwerze maria1, utworzyć tabelę i dodać kilka wierszy. W shellu bazy maria2, używając nieuprzywilejowanego użytkownika, efekt będzie taki: # mysql -u statyk -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.5.9-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show slave status; ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, SLAVE MONITOR privilege(s) for this operation MariaDB [(none)]> use statyk Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [statyk]> select * from dane; +------+--------+ | id | nazwa | +------+--------+ | 3 | trzy | | 4 | cztery | | 5 | piec | +------+--------+ 3 rows in set (0.001 sec) MariaDB [statyk]> insert into dane (id, nazwa) values (8, 'osiem'); ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement MariaDB [statyk]> Jako, że MariaDB w bardzo ograniczonym stopniu pozwala na konfigurację dostępu dla użytkowników z określonych adresów IP, warto upewnić się, że firewall jest poprawnie skonfigurowany. Podczas tworzenia użytkownika //replica_user// można podać mu jeden adres IP serwera maria2, ale wówczas nie można dodać do tego zestawu kolejnych replik.