Mysql - Revision history

Nicolas: /* Create User */

2020-10-03T16:13:38Z

Create User

← Older revision		Revision as of 16:13, 3 October 2020
Line 56:		Line 56:
	</syntaxhighlight>		</syntaxhighlight>
	===== Create User =====		===== Create User =====
			<syntaxhighlight lang="mysql">
			CREATE USER username IDENTIFIED BY 'userpassword';
			</syntaxhighlight>
	<syntaxhighlight lang="mysql">		<syntaxhighlight lang="mysql">
	GRANT ALL PRIVILEGES ON mydatabase.* TO myuser@localhost IDENTIFIED BY 'myuser_password';		GRANT ALL PRIVILEGES ON mydatabase.* TO myuser@localhost IDENTIFIED BY 'myuser_password';
	</syntaxhighlight>		</syntaxhighlight>

	===== Create Table =====		===== Create Table =====
	<syntaxhighlight lang="mysql">		<syntaxhighlight lang="mysql">

Nicolas Rollin at 17:58, 26 June 2016

2016-06-26T17:58:41Z

← Older revision		Revision as of 17:58, 26 June 2016
Line 19:		Line 19:
	###Nr001 bind-address = 127.0.0.1		###Nr001 bind-address = 127.0.0.1
	</syntaxhighlight>		</syntaxhighlight>


	== Syntax ==		== Syntax ==

Nicolas Rollin: /* Installation */

2016-06-26T17:58:11Z

Installation

← Older revision		Revision as of 17:58, 26 June 2016
Line 7:		Line 7:
	<syntaxhighlight lang="bash">		<syntaxhighlight lang="bash">
	apt-get install mysql-server		apt-get install mysql-server
	</syntaxhighlight">		</syntaxhighlight>

	* Recommended		* Recommended
	<syntaxhighlight lang="bash">		<syntaxhighlight lang="bash">
	apt-get install mysql-workbench phpmyadmin		apt-get install mysql-workbench phpmyadmin
	</syntaxhighlight">		</syntaxhighlight>

	=== Setup ===		=== Setup ===
Line 18:		Line 18:
	<syntaxhighlight lang="bash">		<syntaxhighlight lang="bash">
	###Nr001 bind-address = 127.0.0.1		###Nr001 bind-address = 127.0.0.1
	</syntaxhighlight">		</syntaxhighlight>

	== Syntax ==		== Syntax ==

Nicolas Rollin at 17:57, 26 June 2016

2016-06-26T17:57:40Z

← Older revision		Revision as of 17:57, 26 June 2016
Line 1:		Line 1:
			== Installation ==

			Web site [https://www.mysql.com/ MySql]

			=== Package Installation ===
			* MySql Server
			<syntaxhighlight lang="bash">
			apt-get install mysql-server
			</syntaxhighlight">

			* Recommended
			<syntaxhighlight lang="bash">
			apt-get install mysql-workbench phpmyadmin
			</syntaxhighlight">

			=== Setup ===
			* To be accessible from another server remove the bind from /etc/mysql/mysql.conf.d/mysqld.cnf
			<syntaxhighlight lang="bash">
			###Nr001 bind-address = 127.0.0.1
			</syntaxhighlight">

	== Syntax ==		== Syntax ==
	=== Data Manipulation ===		=== Data Manipulation ===

Nicolas Rollin: /* Backup Restore */

2016-05-15T20:29:33Z

Backup Restore

← Older revision		Revision as of 20:29, 15 May 2016
Line 118:		Line 118:
	Backup all database		Backup all database
	<syntaxhighlight lang="mysql">mysqldump --all-databases --single-transaction --user=root --password > all-database.sql</syntaxhighlight>		<syntaxhighlight lang="mysql">mysqldump --all-databases --single-transaction --user=root --password > all-database.sql</syntaxhighlight>

			Backup a specific database

			<syntaxhighlight lang="mysql">mysqldump -h server -p database --add-drop-table --single-transaction --user=root --password</syntaxhighlight>

Nicolas Rollin at 20:12, 15 May 2016

2016-05-15T20:12:30Z

← Older revision		Revision as of 20:12, 15 May 2016
Line 57:		Line 57:
	</syntaxhighlight>		</syntaxhighlight>

	==Maintenance==		== Maintenance ==
	===Binary File===		=== Binary File ===
	* Binary file are usefull to recuperate a database in any point of time		* Binary file are usefull to recuperate a database in any point of time
	* There are usefull for replication		* There are usefull for replication
Line 114:		Line 114:

	</syntaxhighlight>		</syntaxhighlight>

			== Backup Restore ==
			Backup all database
			<syntaxhighlight lang="mysql">mysqldump --all-databases --single-transaction --user=root --password > all-database.sql</syntaxhighlight>

Nicolas Rollin: /* Script */

2014-12-08T16:44:26Z

Script

← Older revision		Revision as of 16:44, 8 December 2014
Line 88:		Line 88:
	===== Script =====		===== Script =====
	<syntaxhighlight lang="bash">		<syntaxhighlight lang="bash">
	#!/bin/bash		#!/bin/bash

	CONFIG_FILE='/exploit/sys_mysql_purgelog.conf'		CONFIG_FILE='/exploit/sys_mysql_purgelog.conf'

	DBHOST=$(cat $CONFIG_FILE \| grep -w DBHOST \| awk -F'=' '{ print $2 }' )		DBHOST=$(cat $CONFIG_FILE \| grep -w DBHOST \| awk -F'=' '{ print $2 }' )
	DBUSER=$(cat $CONFIG_FILE \| grep -w DBUSER \| awk -F'=' '{ print $2 }' )		DBUSER=$(cat $CONFIG_FILE \| grep -w DBUSER \| awk -F'=' '{ print $2 }' )
	DBUSERPWD=$(cat $CONFIG_FILE \| grep -w DBUSERPWD \| awk -F'=' '{ print $2 }' )		DBUSERPWD=$(cat $CONFIG_FILE \| grep -w DBUSERPWD \| awk -F'=' '{ print $2 }' )
	DBPORT=$(cat $CONFIG_FILE \| grep -w DBPORT \| awk -F'=' '{ print $2 }' )		DBPORT=$(cat $CONFIG_FILE \| grep -w DBPORT \| awk -F'=' '{ print $2 }' )

	purgedt=$(mysql -h $DBHOST -u $DBUSER -P $DBPORT --password=$DBUSERPWD -e "select date_sub(current_date(), interval 7 day)" -s -N )		purgedt=$(mysql -h $DBHOST -u $DBUSER -P $DBPORT --password=$DBUSERPWD -e "select date_sub(current_date(), interval 7 day)" -s -N )
	echo "Purge binary logs before $purgedt started at $(date +%m%d%y_%H:%M)"		echo "Purge binary logs before $purgedt started at $(date +%m%d%y_%H:%M)"
	str="PURGE BINARY LOGS BEFORE '"$purgedt"'"		str="PURGE BINARY LOGS BEFORE '"$purgedt"'"
	mysql $DB -h $DBHOST -u $DBUSER -P $DBPORT --password=$DBUSERPWD -e "$str"		mysql $DB -h $DBHOST -u $DBUSER -P $DBPORT --password=$DBUSERPWD -e "$str"

	success=$?		success=$?

	if [ $success = 0 ];		if [ $success = 0 ];
	then		then
	echo "Purge binary logs before $purgedt completed at $(date +%m%d%y_%H:%M)"		echo "Purge binary logs before $purgedt completed at $(date +%m%d%y_%H:%M)"
	else		else
	echo "!!!Failed!!!"		echo "!!!Failed!!!"
	fi #if [ $success = 0 ] then		fi #if [ $success = 0 ] then
	exit 0
			exit 0

	</syntaxhighlight>		</syntaxhighlight>

Nicolas Rollin: /* Maintenance */

2014-12-08T16:43:30Z

Maintenance

← Older revision		Revision as of 16:43, 8 December 2014
Line 73:		Line 73:

	The binary logs in mysql-bin.index is expected to be <span style="color:red">numerically consecutive</span>. If the binary logs are not numerically consecutive, log rotation is <span style="color:red">disabled</span>.		The binary logs in mysql-bin.index is expected to be <span style="color:red">numerically consecutive</span>. If the binary logs are not numerically consecutive, log rotation is <span style="color:red">disabled</span>.

			==== Script for removing binary file ====
			===== Config File =====
			<syntaxhighlight lang="bash">
			#/bin/bash
			#DB Connection paramters
			DBHOST=0.0.0.0
			DBNAME=test
			DBUSER=dbuser
			DBUSERPWD=userpassword
			DBPORT=3306

			</syntaxhighlight>
			===== Script =====
			<syntaxhighlight lang="bash">
			#!/bin/bash

			CONFIG_FILE='/exploit/sys_mysql_purgelog.conf'

			DBHOST=$(cat $CONFIG_FILE \| grep -w DBHOST \| awk -F'=' '{ print $2 }' )
			DBUSER=$(cat $CONFIG_FILE \| grep -w DBUSER \| awk -F'=' '{ print $2 }' )
			DBUSERPWD=$(cat $CONFIG_FILE \| grep -w DBUSERPWD \| awk -F'=' '{ print $2 }' )
			DBPORT=$(cat $CONFIG_FILE \| grep -w DBPORT \| awk -F'=' '{ print $2 }' )

			purgedt=$(mysql -h $DBHOST -u $DBUSER -P $DBPORT --password=$DBUSERPWD -e "select date_sub(current_date(), interval 7 day)" -s -N )
			echo "Purge binary logs before $purgedt started at $(date +%m%d%y_%H:%M)"
			str="PURGE BINARY LOGS BEFORE '"$purgedt"'"
			mysql $DB -h $DBHOST -u $DBUSER -P $DBPORT --password=$DBUSERPWD -e "$str"

			success=$?

			if [ $success = 0 ];
			then
			echo "Purge binary logs before $purgedt completed at $(date +%m%d%y_%H:%M)"
			else
			echo "!!!Failed!!!"
			fi #if [ $success = 0 ] then
			exit 0

			</syntaxhighlight>

Nicolas Rollin: /* Maintenance */

2014-12-08T16:38:28Z

Maintenance

← Older revision		Revision as of 16:38, 8 December 2014
Line 71:		Line 71:
	binlog_format = 'MIXED'		binlog_format = 'MIXED'
	</syntaxhighlight>		</syntaxhighlight>

			The binary logs in mysql-bin.index is expected to be <span style="color:red">numerically consecutive</span>. If the binary logs are not numerically consecutive, log rotation is <span style="color:red">disabled</span>.

Nicolas Rollin at 16:34, 8 December 2014

2014-12-08T16:34:05Z

← Older revision		Revision as of 16:34, 8 December 2014
Line 55:		Line 55:
	);		);

			</syntaxhighlight>

			==Maintenance==
			===Binary File===
			* Binary file are usefull to recuperate a database in any point of time
			* There are usefull for replication
			* But as all informationare written into the logfile before updating the database, you may expect an impact on the perfomance

			==== Setup ====
			<syntaxhighlight lang="bash">
			log_bin = /var/log/mysql/mysql-bin.log
			expire_logs_days = 10
			max_binlog_size = 250M
			binlog_do_db = include_database_name
			binlog_format = 'MIXED'
	</syntaxhighlight>		</syntaxhighlight>