MariaDB: Difference between revisions

Line 30:

By default, MySQL uses InnoDB to store the information in the database. The old method, MyIASM stored the data within the database folder on the hard drive separately. InnoDB now stores this data for all created databases under one file called <code>ibdata1</code>. There are a few problems with this implementation:

* All data stored under one file means if this one file becomes corrupt, you are more likely to loose '''ALL''' the databases you have.

* A problem in InnoDB makes it so that when you drop a table, the space is not freed from the file, the table is basically unlinked from the data. This can be a security issue if you ~~want to destroy the~~ data. Another problem means that the file will never shrink, the file will grow until all space is exhausted.

* A problem in InnoDB makes it so that when you drop a table, the space is not freed from the file, the table is basically unlinked from the data. This can be a security issue if you are working with sensitive data. Another problem means that the file will never shrink, the file will grow until all space is exhausted.

* Once set up in the mode, any database created before this change will still be stored in the one file. The only way to make it use it's own table is by [[Exporting a MySQL database|exporting]] the database dropping the table and then [[Importing a MySQL database|importing]] the database again.

MySQL does let you fall back to the old MyIASM method of storing the data while using the InnoDB storage method still. If you want to turn this option on, you will need to do the following:

Line 60:

Line 61:

<li><ul><li> Goto the Lanuch menu and open Konsole</li>

<li> At the prompt type <code>rcmysql restart</code>. This can take a few seconds.</li>

<li> Now type <code>rcmysql status</code>. You should see that the sever is now running. If it isn't and it says 'failed', there is a problem with what you have typed. You will need to go back through from the ~~beggining~~, ensuring you have typed the directive correctly and have placed it in the correct block.</li>

<li> Now type <code>rcmysql status</code>. You should see that the sever is now running. If it isn't and it says 'failed', there is a problem with what you have typed. You will need to go back through from the beginning, ensuring you have typed the directive correctly and have placed it in the correct block.</li>

</ul></ol>

=== Binary Logging ===

MySQL by default turns on a feature called binary logging. This makes it so that every transaction within the database, including the data, is stored in a binary log. That way, if you are running more than 1 MySQL server in a master-slave configuration, you can link them together and the slave reads the logs to get the same data. You more than likely won't need this if you are running a standalone MySQL setup. The problems with this implementation are:

* MySQL has to write the transaction twice, once to the table and once to the log. This slows things down.

* MySQL has data stored in 2 places, doubling the disk space for each write. This is also a security issue if working with sensitive data.

If you are not using a MySQL duplication server, the logging is of no use. It can be turned off by the following:

<ol>

<li>Goto the Launch Menu and launch Kwrite.</li>

<li>You then need to goto 'File' on the menu bar and click 'Open'.</li>

<li>You now need to open the MySQL configuration file. This is located at <code>/etc/my.cnf</code>.</li>

<li>Scroll down and find the block:

<pre># Replication Master Server (default)

# binary logging is required for replication

log-bin=mysql-bin

# binary logging format - mixed recommended

binlog_format=mixed</pre></li>

<li>You now need to comment out the lines <code>log-bin=mysql-bin</code> and <code>binlog_format<code>. This is done by adding a <code>#</code> to the beginning of the lines. Once commented out, the block should look like this:

<pre># Replication Master Server (default)

# binary logging is required for replication

# log-bin=mysql-bin

# binary logging format - mixed recommended

# binlog_format=mixed</pre></li>

<li>Now save this change and close the file.</li>

<li>For the change to take effect, you will need to restart the MySQL server.</li>

<li><ul><li> Goto the Lanuch menu and open Konsole</li>

<li> At the prompt type <code>rcmysql restart</code>. This can take a few seconds.</li>

<li> Now type <code>rcmysql status</code>. You should see that the sever is now running. If it isn't and it says 'failed', there is a problem with what you have typed. You will need to go back through from the beginning, ensuring you have put the <code>#</code> in front of the lines of code shown.</li>

</ul></ol>

== External ==

[http://www.mysql.com MySQL website]

@@ Line 30: / Line 30: @@
 By default, MySQL uses InnoDB to store the information in the database. The old method, MyIASM stored the data within the database folder on the hard drive separately. InnoDB now stores this data for all created databases under one file called <code>ibdata1</code>. There are a few problems with this implementation:
 * All data stored under one file means if this one file becomes corrupt, you are more likely to loose '''ALL''' the databases you have.
-* A problem in InnoDB makes it so that when you drop a table, the space is not freed from the file, the table is basically unlinked from the data. This can be a security issue if you want to destroy the data. Another problem means that the file will never shrink, the file will grow until all space is exhausted.
+* A problem in InnoDB makes it so that when you drop a table, the space is not freed from the file, the table is basically unlinked from the data. This can be a security issue if you are working with sensitive data. Another problem means that the file will never shrink, the file will grow until all space is exhausted.
+* Once set up in the mode, any database created before this change will still be stored in the one file. The only way to make it use it's own table is by [[Exporting a MySQL database|exporting]] the database dropping the table and then [[Importing a MySQL database|importing]] the database again.
 MySQL does let you fall back to the old MyIASM method of storing the data while using the InnoDB storage method still. If you want to turn this option on, you will need to do the following:
@@ Line 60: / Line 61: @@
 <li><ul><li> Goto the Lanuch menu and open Konsole</li>
 <li> At the prompt type <code>rcmysql restart</code>. This can take a few seconds.</li>
-<li> Now type <code>rcmysql status</code>. You should see that the sever is now running. If it isn't and it says 'failed', there is a problem with what you have typed. You will need to go back through from the beggining, ensuring you have typed the directive correctly and have placed it in the correct block.</li>
+<li> Now type <code>rcmysql status</code>. You should see that the sever is now running. If it isn't and it says 'failed', there is a problem with what you have typed. You will need to go back through from the beginning, ensuring you have typed the directive correctly and have placed it in the correct block.</li>
 </ul></ol>
+=== Binary Logging ===
+MySQL by default turns on a feature called binary logging. This makes it so that every transaction within the database, including the data, is stored in a binary log. That way, if you are running more than 1 MySQL server in a master-slave configuration, you can link them together and the slave reads the logs to get the same data. You more than likely won't need this if you are running a standalone MySQL setup. The problems with this implementation are:
+* MySQL has to write the transaction twice, once to the table and once to the log. This slows things down.
+* MySQL has data stored in 2 places, doubling the disk space for each write. This is also a security issue if working with sensitive data.
+If you are not using a MySQL duplication server, the logging is of no use. It can be turned off by the following:
+<ol>
+<li>Goto the Launch Menu and launch Kwrite.</li>
+<li>You then need to goto 'File' on the menu bar and click 'Open'.</li>
+<li>You now need to open the MySQL configuration file. This is located at <code>/etc/my.cnf</code>.</li>
+<li>Scroll down and find the block:
+<pre># Replication Master Server (default)
+# binary logging is required for replication
+log-bin=mysql-bin
+# binary logging format - mixed recommended
+binlog_format=mixed</pre></li>
+<li>You now need to comment out the lines <code>log-bin=mysql-bin</code> and <code>binlog_format<code>. This is done by adding a <code>#</code> to the beginning of the lines. Once commented out, the block should look like this:
+<pre># Replication Master Server (default)
+# binary logging is required for replication
+# log-bin=mysql-bin
+# binary logging format - mixed recommended
+# binlog_format=mixed</pre></li>
+<li>Now save this change and close the file.</li>
+<li>For the change to take effect, you will need to restart the MySQL server.</li>
+<li><ul><li> Goto the Lanuch menu and open Konsole</li>
+<li> At the prompt type <code>rcmysql restart</code>. This can take a few seconds.</li>
+<li> Now type <code>rcmysql status</code>. You should see that the sever is now running. If it isn't and it says 'failed', there is a problem with what you have typed. You will need to go back through from the beginning, ensuring you have put the <code>#</code> in front of the lines of code shown.</li>
+</ul></ol>
 == External ==
 [http://www.mysql.com MySQL website]