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