MariaDB/MySQL - Revision history

Hdridder: /* Login to password user without plain text password on commandline */

2026-10-04T13:21:51Z

← Older revision		Revision as of 13:21, 4 October 2026
Line 21:		Line 21:
	Put the password in a protected config file.		Put the password in a protected config file.

	vi ~/.my.~~conf~~		vi ~/.my.cnf
	[client]		[client]
	password=<password>		password=<password>

Hdridder: /* Login to password user without plain text password on commandline */

2026-10-04T13:09:15Z

← Older revision		Revision as of 13:09, 4 October 2026
Line 27:		Line 27:
	chmod 600 ~/.my.cnf		chmod 600 ~/.my.cnf

	When you want to use a different filename start mysql-~~clinet~~ like		When you want to use a different filename start the mysql-client like
	mysql --defaults-file=/home/francis/mysql-opts		mysql --defaults-file=/home/francis/mysql-opts

Hdridder at 19:50, 9 June 2026

2026-06-09T19:50:47Z

← Older revision		Revision as of 19:50, 9 June 2026
Line 10:		Line 10:

	=User management=		=User management=
			User data is stored in table mysql.user
	;alter user username@host identified by 'password'		;alter user username@host identified by 'password'
	:Change a users password		:Change a users password

Hdridder: /* User management */

2024-09-09T13:15:38Z

User management

@@ Line 16: / Line 16: @@
 ;grans all privileges on *.* to 'user'@'host';
 :Grant all privileges to all database to a user
 =Move users from one server to another=

Hdridder: /* Repair replication */

2023-01-20T16:53:22Z

Repair replication

@@ Line 134: / Line 134: @@
 ;<code>SET GLOBAL gtid_slave_pos = ""</code>
 :Reset counter, slave databases must be in sync with the master.
 =Repair replication=

Hdridder: /* On the slave server */

2023-01-20T15:27:42Z

On the slave server

← Older revision		Revision as of 15:27, 20 January 2023
Line 147:		Line 147:
	==On the slave server==		==On the slave server==
	;Import the databases		;Import the databases
	<code>mysql -uroot -p < <~~mysql_dump_...sql.dump~~</code>		<code>mysql -uroot -p < <dumpfile></code>
	;Restart the slave processes and check the slave status.		;Restart the slave processes and check the slave status.
	mysql ~~-u root -p~~		When needed update the slave log-file and position as in the masters <code>show slave status</code>
			mysql> CHANGE MASTER TO MASTER_LOG_FILE='<filename>', MASTER_LOG_POS=<postition>;
			Then:
	mysql> start slave;		mysql> start slave;
	mysql> show slave status \G		mysql> show slave status \G

			This should not show any errors

	=Populate and update timezone information=		=Populate and update timezone information=

Hdridder: /* On the master */

2023-01-20T11:21:30Z

On the master

← Older revision		Revision as of 11:21, 20 January 2023
Line 65:		Line 65:
	mysql> FLUSH PRIVILEGES;		mysql> FLUSH PRIVILEGES;

			;Make sure nothing is changes in the database during backup
	mysql> USE exampledb;		mysql> USE exampledb;
	mysql> FLUSH TABLES WITH READ LOCK;		mysql> FLUSH TABLES WITH READ LOCK;
			Or, for all databases;
			mysql> FLUSH TABLES WITH READ LOCK;
			mysql> SET GLOBAL read_only = 1;
			;Check status;
	mysql> SHOW MASTER STATUS;		mysql> SHOW MASTER STATUS;
	+---------------+----------+--------------+------------------+		+---------------+----------+--------------+------------------+
Line 80:		Line 85:
	;Unlock the tables		;Unlock the tables
	mysql -u root -p<password>		mysql -u root -p<password>
	mysql>UNLOCK TABLES;		mysql> UNLOCK TABLES;
	mysql> quit;		mysql> quit;
			Or, for all databases;
			mysql> SET GLOBAL read_only = 0;
			mysql> UNLOCK TABLES;

	==On the slave server==		==On the slave server==

Hdridder: /* On the slave server */

2022-12-17T14:52:30Z

On the slave server

← Older revision		Revision as of 14:52, 17 December 2022
Line 86:		Line 86:

	;Create the slave database		;Create the slave database
	mysql -u root -p		<code>mysql -u root -p<password></code>
	~~mysql~~> CREATE DATABASE exampledb;		<syntaxhighlight lang=sql>
	~~mysql>~~ quit;		CREATE DATABASE exampledb;
			quit;
			</syntaxhighlight>

	;Load the database dump created on the master		;Load the database dump created on the master
	mysql -u root -p<password> exampledb < /path/to/exampledb.sql		<code>mysql -u root -p<password> exampledb < /path/to/exampledb.sql</code>

	;Edit the my.cnf so it has these lines		;Edit the my.cnf so it has these lines
	:The server-id must be unique in you network		:The server-id must be unique in you network
	:The replicate-do-db specifies for which database the binlog on the master is read, one line per database. If omitted for all databases binlog is read.		:The replicate-do-db specifies for which database the binlog on the master is read, one line per database. If omitted for all databases binlog is read.
	server-id=2		<syntaxhighlight lang=text>
	replicate-do-db=exampledb		server-id=2
			replicate-do-db=exampledb
			</syntaxhighlight>

	;Restart the database server		;Restart the database server
	/etc/init.d/mysql restart		<code>/etc/init.d/mysql restart</code>

	In the following statement the values from SHOW MASTER STATUS above must be used.		In the following statement the values from SHOW MASTER STATUS above must be used.
	mysql -u root -p<password>
	~~mysql~~> STOP SLAVE;		<code>mysql -u root -p<password></code>
	~~mysql>~~ CHANGE MASTER TO MASTER_HOST=<masterIP>, MASTER_USER='slave_user', MASTER_PASSWORD='<password>', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183;		<syntaxhighlight lang=sql>
	~~mysql>~~ START SLAVE;		STOP SLAVE;
	mysql> ~~quit~~;		CHANGE MASTER TO MASTER_HOST=<masterIP>, MASTER_USER='slave_user', MASTER_PASSWORD='<password>', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183;
			START SLAVE;
			quit;
			</syntaxhighlight>

			====In case of using GTID for replication====
			<code>mysql -u root -p<password></code>
			<syntaxhighlight lang=sql>
			CHANGE MASTER TO master_host= <masterIP>', master_user='<slave_user>', MASTER_PASSWORD='<password>', master_use_gtid=current_pos;
			</syntaxhighlight>

			;<code>SELECT @@GLOBAL.gtid_slave_pos;</code>
			:Show current GTID position
			;<code>SET GLOBAL gtid_slave_pos = ""</code>
			:Reset counter, slave databases must be in sync with the master.

	=Repair replication=		=Repair replication=

Hdridder: /* On the master */

2022-01-02T13:42:05Z

On the master

← Older revision		Revision as of 13:42, 2 January 2022
Line 40:		Line 40:

	This procedure is for a large part based on a tutorial found on howtoforge.com [https://www.howtoforge.com/mysql_database_replication]		This procedure is for a large part based on a tutorial found on howtoforge.com [https://www.howtoforge.com/mysql_database_replication]

			For multi master replication to 1 slave check [https://mariadb.com/kb/en/multi-source-replication/ this].

	==On the master==		==On the master==

Hdridder: /* On the master */

2022-01-02T11:27:22Z

On the master

← Older revision		Revision as of 11:27, 2 January 2022
Line 44:		Line 44:
	;Edit the my.cnf so it has below lines.		;Edit the my.cnf so it has below lines.
	:The server must be accessible from the slave server and the server-id must be unique in you network		:The server must be accessible from the slave server and the server-id must be unique in you network
	:The binlog-do-db specifies for which database the binlog is written, one line per database. If omitted for all databases binlog is written ~~which is the easiest setup~~.		:The binlog-do-db specifies for which database the binlog is written, one line per database. If omitted for all databases binlog is written except those specified by binlog-ingnore-db.
	#skip-networking		#skip-networking
	#bind-address = 127.0.0.1		#bind-address = 127.0.0.1
	log-bin = /var/log/mysql/mysql-bin.log		log-bin = /var/log/mysql/mysql-bin.log
	#binlog-do-db=exampledb		#binlog-do-db=exampledb
			binlog-ignore-db = mysql
			binlog-ignore-db = information_schema
			binlog-ignore-db = performance_schema
			binlog-ignore-db = phpmyadmin
	server-id=1		server-id=1