[Maria-discuss] flush tables with read lock blocks
Hi, I'm using mariadb-10.5.18-1.fc37.x86_64 on fedora37 set up as a master server. There are currently two or three slaves configured and operating properly. I'm trying to add another slave, and for some reason when I try to flush tables with read lock, then dump the databases to be transferred to the new slave, mysqldump just prints:
-- Connecting to localhost...
At first I didn't realize it, but soon the database stopped responding to all clients until I released the lock with "unlock tables".
I'm following these directions:
https://mariadb.com/kb/en/setting-up-replication/
What could I be doing wrong? Here are the my.cnf options that I believe could be related to my issue. Any ideas greatly appreciated.
The server has 128GB of RAM.
[mysqld] sql_mode=ONLY_FULL_GROUP_BY character-set-client-handshake = FALSE character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci max_connections=1000 replicate_do_db='repdb' replicate_do_db='sbclient' port = 3306 socket = /var/lib/mysql/mysql.sock skip-external-locking key_buffer_size = 256M max_allowed_packet = 512M join_buffer_size = 2M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M query_cache_size = 400000000 query_cache_type = 1 query_cache_strip_comments = 1 relay_log_space_limit = 500M relay_log_purge = 1 log-slave-updates = 1 local_infile = OFF binlog_format = ROW max_heap_table_size = 2048M tmp_table_size = 2048M relay-log=myserver-relay-bin log_bin = /var/log/mariadb/mysql-bin.log log_warnings = 1 expire_logs_days = 2 max_binlog_size = 500M plugin_load=server_audit=server_audit.so plugin_load_add = query_response_time server_audit_events=connect,query server_audit_file_path = /var/log/mariadb/server_audit.log server_audit_file_rotate_size = 1G server_audit_file_rotations = 1 slow-query-log = 1 slow-query-log-file = /var/log/mariadb/mariadb-slow.log long_query_time = 1 log_error = /var/log/mariadb/mariadb-error.log skip-networking=0 binlog_format=mixed server-id = 590
Hello. The read lock is only to get the masters coordinates, but that can also be done with --master-data and/or --gtid, depending on how your doing replication. The fact that you can't dump a system that has a session open with read lock, is expected, all that you really want to do during that time is get the coordinates, IMO that page should be updated. You can see similar comments saying so.
Good Luck. I normally just use mariabackup for restores, but have this in my mysqldump creation script, i used to use to seed replicas DEFAULT_ARGS="--all-databases --gtid --routines --events" if [ "${NODE_TYPE}" = "replica" ]; then NODE_OPT="--master-data"; else echo "Coming soon, bye" exit 1 fi
On Tue, Dec 13, 2022 at 12:01 PM Alex mysqlstudent@gmail.com wrote:
Hi, I'm using mariadb-10.5.18-1.fc37.x86_64 on fedora37 set up as a master server. There are currently two or three slaves configured and operating properly. I'm trying to add another slave, and for some reason when I try to flush tables with read lock, then dump the databases to be transferred to the new slave, mysqldump just prints:
-- Connecting to localhost...
At first I didn't realize it, but soon the database stopped responding to all clients until I released the lock with "unlock tables".
I'm following these directions:
https://mariadb.com/kb/en/setting-up-replication/
What could I be doing wrong? Here are the my.cnf options that I believe could be related to my issue. Any ideas greatly appreciated.
The server has 128GB of RAM.
[mysqld] sql_mode=ONLY_FULL_GROUP_BY character-set-client-handshake = FALSE character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci max_connections=1000 replicate_do_db='repdb' replicate_do_db='sbclient' port = 3306 socket = /var/lib/mysql/mysql.sock skip-external-locking key_buffer_size = 256M max_allowed_packet = 512M join_buffer_size = 2M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M query_cache_size = 400000000 query_cache_type = 1 query_cache_strip_comments = 1 relay_log_space_limit = 500M relay_log_purge = 1 log-slave-updates = 1 local_infile = OFF binlog_format = ROW max_heap_table_size = 2048M tmp_table_size = 2048M relay-log=myserver-relay-bin log_bin = /var/log/mariadb/mysql-bin.log log_warnings = 1 expire_logs_days = 2 max_binlog_size = 500M plugin_load=server_audit=server_audit.so plugin_load_add = query_response_time server_audit_events=connect,query server_audit_file_path = /var/log/mariadb/server_audit.log server_audit_file_rotate_size = 1G server_audit_file_rotations = 1 slow-query-log = 1 slow-query-log-file = /var/log/mariadb/mariadb-slow.log long_query_time = 1 log_error = /var/log/mariadb/mariadb-error.log skip-networking=0 binlog_format=mixed server-id = 590
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Hi,
On Tue, Dec 13, 2022 at 12:39 PM Jeff Dyke jeff.dyke@gmail.com wrote:
Hello. The read lock is only to get the masters coordinates, but that can also be done with --master-data and/or --gtid, depending on how your doing replication. The fact that you can't dump a system that has a session open with read lock, is expected, all that you really want to do during that time is get the coordinates, IMO that page should be updated. You can see similar comments saying so.
Thanks very much for your help. I also have a mariabackup command that regularly backs up all databases, but I'm hoping I never have to restore, because it looks complicated. I found a shell script on github that creates periodically creates a full backup, then creates some number of daily incremental backups associated with the full backup that all have to be restored properly. https://github.com/omegazeng/run-mariabackup/blob/master/run-mariabackup.sh
Maybe just doing a full backup using mariabackup in this instance wouldn't be as complex.
I was just now able to get it working with "flush tables with read locks" with the following mysqldump command:
/usr/bin/mysqldump --skip-opt --single-transaction --add-drop-table \ --create-options --quick --extended-insert --set-charset --disable-keys \ --verbose -u root -p'password' --compress \ --log-error=$basedir/backup-$dbase-$today-error.log \ --result-file=$basedir/backup-$dbase-$today.sql $dbase
I'm not replicating all databases, so figured this would be a good individual option.
Thanks again, Alex
Good Luck. I normally just use mariabackup for restores, but have this in my mysqldump creation script, i used to use to seed replicas DEFAULT_ARGS="--all-databases --gtid --routines --events" if [ "${NODE_TYPE}" = "replica" ]; then NODE_OPT="--master-data"; else echo "Coming soon, bye" exit 1 fi
On Tue, Dec 13, 2022 at 12:01 PM Alex mysqlstudent@gmail.com wrote:
Hi, I'm using mariadb-10.5.18-1.fc37.x86_64 on fedora37 set up as a master server. There are currently two or three slaves configured and operating properly. I'm trying to add another slave, and for some reason when I try to flush tables with read lock, then dump the databases to be transferred to the new slave, mysqldump just prints:
-- Connecting to localhost...
At first I didn't realize it, but soon the database stopped responding to all clients until I released the lock with "unlock tables".
I'm following these directions:
https://mariadb.com/kb/en/setting-up-replication/
What could I be doing wrong? Here are the my.cnf options that I believe could be related to my issue. Any ideas greatly appreciated.
The server has 128GB of RAM.
[mysqld] sql_mode=ONLY_FULL_GROUP_BY character-set-client-handshake = FALSE character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci max_connections=1000 replicate_do_db='repdb' replicate_do_db='sbclient' port = 3306 socket = /var/lib/mysql/mysql.sock skip-external-locking key_buffer_size = 256M max_allowed_packet = 512M join_buffer_size = 2M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M query_cache_size = 400000000 query_cache_type = 1 query_cache_strip_comments = 1 relay_log_space_limit = 500M relay_log_purge = 1 log-slave-updates = 1 local_infile = OFF binlog_format = ROW max_heap_table_size = 2048M tmp_table_size = 2048M relay-log=myserver-relay-bin log_bin = /var/log/mariadb/mysql-bin.log log_warnings = 1 expire_logs_days = 2 max_binlog_size = 500M plugin_load=server_audit=server_audit.so plugin_load_add = query_response_time server_audit_events=connect,query server_audit_file_path = /var/log/mariadb/server_audit.log server_audit_file_rotate_size = 1G server_audit_file_rotations = 1 slow-query-log = 1 slow-query-log-file = /var/log/mariadb/mariadb-slow.log long_query_time = 1 log_error = /var/log/mariadb/mariadb-error.log skip-networking=0 binlog_format=mixed server-id = 590
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (2)
-
Alex -
Jeff Dyke