maria-discuss team mailing list archive

[Alex <mysqlstudent@xxxxxxxxx>]

Hi,

On Tue, Dec 13, 2022 at 12:39 PM Jeff Dyke <jeff.dyke@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~maria-discuss
>> More help   : https://help.launchpad.net/ListHelp
>>
>