MySQL - Revision history

Gr0x0rd: /* Installing mySQL */

2013-10-06T23:04:17Z

Installing mySQL

Gr0x0rd: /* Installing mySQL */

2013-10-06T23:04:06Z

Installing mySQL

Gr0x0rd: /* mySQL Replication */

2013-10-06T22:47:09Z

mySQL Replication

Gr0x0rd: /* mySQL Replication */

2013-10-06T22:23:06Z

mySQL Replication

Gr0x0rd at 21:30, 6 October 2013

2013-10-06T21:30:19Z

Gr0x0rd: /* Restoring mySQL */

2011-06-15T17:45:17Z

Restoring mySQL

Gr0x0rd: /* Backing up mySQL */

2011-06-15T17:43:50Z

Backing up mySQL

Gr0x0rd: /* mySQL */

2011-05-09T15:59:41Z

mySQL

Gr0x0rd: Created page with "== mySQL == mySQL is a popular open-source database... well, it used to be. It has since been gobbled up by the monster that is Sun/Oracle. While aspects of the database remain ..."

2011-05-09T15:54:48Z

Created page with "== mySQL == mySQL is a popular open-source database... well, it used to be. It has since been gobbled up by the monster that is Sun/Oracle. While aspects of the database remain ..."

New page

== mySQL ==

mySQL is a popular open-source database... well, it used to be. It has since been gobbled up by the monster that is Sun/Oracle. While aspects of the database remain open source, many of the open-source community are gradually moving away from mySQL. That said, it is still arguably the most convenient and widely used non-commercial database product in the world.

Chances are mysql has already been emerged on your system as a dependency for some other package. But in case it hasn't
'''$''' sudo emerge -av mysql
After the package has emerged, we'll need to set it up, start it, and add it to startup.
'''$''' sudo /usr/bin/mysql_install_db
'''$''' sudo /etc/init.d/mysql start
'''$''' sudo rc-update add mysql default
Next we'll set a root password for mySQL.
'''$''' sudo /usr/bin/mysqladmin -u root password '<yourpassword>'
To log in to mysql, type
'''$''' mysql -u root -p
and enter the password you supplied above.

To create a database, enter
mysql> CREATE DATABASE <yourdatabase>;

To create a user, enter
mysql> CREATE USER 'yourmysqluser'@'localhost';

To set their password, enter
mysql> set password for <yourmysqluser>@localhost = password('<yourpassword>');

To grant that user privileges full permissions on a database, enter
mysql> grant ALL on <yourdatabase>.* to <yourmysqluser>@localhost;

Finally, to exit mySQL, enter
mysql> quit

@@ Line 68: / Line 68: @@
 On each slave run the following commands:
   mysql> CHANGE MASTER TO MASTER_HOST=' master_host_name ', MASTER_USER='replicaslave', MASTER_PASSWORD=' memorable password ', MASTER_LOG_FILE=' recorded_log_file_name ', MASTER_LOG_POS=recorded_log_position;
-Note the lack of quotes in the final command.
+Note the lack of quotes in the final command. If all is well, start the slave instance.
-(to be completed)
+ mysql> start slave;

← Older revision		Revision as of 22:23, 6 October 2013
Line 51:		Line 51:

	To create a replicated or mirrored mySQL database environment on another server, first create a user in both environments. In this case we'll use a user called ''replicaslave''.		To create a replicated or mirrored mySQL database environment on another server, first create a user in both environments. In this case we'll use a user called ''replicaslave''.
−	mysql>	+	mysql> CREATE USER 'replicaslave'@'%';
		+	mysql> grant replication slave on . to 'replicaslave';
		+	Add the following lines to the /etc/mysql/my.cnf files of each server.
		+	log-bin=mysql-bin
		+	binlog_format=mixed
		+	port=3306
		+	server-id = <Your number here>
		+	The master server should have server-id 1, all slaves should have a unique server-id. <br><br>
		+	Restart the mysql service on each machine. To test the changes, log into each instance, and issue the following command:
		+	mysql> show global variables like 'server_id';
		+	On the master, it should show that the server_id is 1, and on each slave, make sure that the server id is not 1 or 0, and that no two servers have the same id.
		+	<br><Br>
		+	If all is well, on the Master, run the following commands, and note the value of the ''File'' and ''Position'' returned.
		+	mysql> Flush tables with read lock;
		+	mysql> show master status;
		+	On each slave run the following commands:
		+	mysql> CHANGE MASTER TO MASTER_HOST=' master_host_name ', MASTER_USER='replicaslave', MASTER_PASSWORD=' memorable password ', MASTER_LOG_FILE=' recorded_log_file_name ', MASTER_LOG_POS=recorded_log_position;
		+	Note the lack of quotes in the final command.
		+	(to be completed)

← Older revision		Revision as of 21:30, 6 October 2013
Line 47:		Line 47:
	To restore mySQL based on a backup file you've created, simply run the command		To restore mySQL based on a backup file you've created, simply run the command
	'''$''' gunzip < /path/to/backupfile.sql.gz \| mysql -u root -p		'''$''' gunzip < /path/to/backupfile.sql.gz \| mysql -u root -p
		+
		+	== mySQL Replication ==
		+
		+	To create a replicated or mirrored mySQL database environment on another server, first create a user in both environments. In this case we'll use a user called ''replicaslave''.
		+	mysql>

← Older revision		Revision as of 17:45, 15 June 2011
Line 46:		Line 46:

	To restore mySQL based on a backup file you've created, simply run the command		To restore mySQL based on a backup file you've created, simply run the command
−	'''$''' ~~mysql -u root -p~~ < /path/to/~~yourbackup~~.sql	+	'''$''' gunzip < /path/to/backupfile.sql.gz \| mysql -u root -p

@@ Line 38: / Line 38: @@
   datum=`/bin/date +%Y%m%d-%H`
   /usr/bin/mysqldump --user=root --password=<yourpassword> --lock-all-tables \
-  --all-databases > /path/to/backup-${datum}.sql
+  --all-databases | gzip -9 > /path/to/backup-${datum}.sql.gz
 Make the file executable
   '''$''' sudo chmod 775 /usr/bin/backupmysql
-You can now back up your entire mySQL database by running this script.
+You can now back up your entire mySQL database by running this script. If you run it manually rather than via cron, you should make sure you have write permissions to the /path/to folder.
 == Restoring mySQL ==

@@ Line 29: / Line 29: @@
 Finally, to exit mySQL, enter
   mysql> quit
 == Backing up mySQL ==

@@ Line 1: / Line 1: @@
-== mySQL ==
+== Installing mySQL ==
 mySQL is a popular open-source database... well, it used to be. It has since been gobbled up by the monster that is Sun/Oracle. While aspects of the database remain open source, many of the open-source community are gradually moving away from mySQL. That said, it is still arguably the most convenient and widely used non-commercial database product in the world.
@@ Line 29: / Line 29: @@
 Finally, to exit mySQL, enter
   mysql> quit