MariaDB: Difference between revisions

Line 20:

# Type your password when asked.

# If you've logged in OK, exit by typing: <pre>mysql> exit;</pre>(Don't forget the ;). MySQL will respond with 'Bye' if you have correctly exited and return you to the prompt.

# If your stuck and MySQL is returning <code>-></code> at any time, you have forgotten the ; at the end of the command. Type the command with a ;. This will display an error message. Type the command again with the ; and the command will work

# If your stuck and MySQL is returning <code>-></code> at any time, you have forgotten the ; at the end of the command. Type the command with a ;. This will display an error message. Type the command again with the ; and the command will work.

== Configuring MySQL ==

There are a few things you may want to change with MySQL, depending on how you plan on using MySQL.

=== InnoDB separate tables ===

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.

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:

<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 (values edited out for security):<br></li>

# The MySQL server

[mysqld]

port = 3306

socket = /<dir>/mysql.sock

# Change following line if you want to store your database elsewhere<br>

# datadir = /<dir>

skip-external-locking

key_buffer_size = <num>M

max_allowed_packet = <num>M

table_open_cache = <num>

sort_buffer_size = <num>K

net_buffer_length = <num>K

read_buffer_size = <num>K

read_rnd_buffer_size = <num>K

myisam_sort_buffer_size = <num>M

<li>At the end of the block, you will need to add the following:</li>

innodb_file_per_table=1

<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>* 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>

</ol>

== External ==

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

@@ Line 20: / Line 20: @@
 # Type your password when asked.
 # If you've logged in OK, exit by typing: <pre>mysql> exit;</pre>(Don't forget the ;). MySQL will respond with 'Bye' if you have correctly exited and return you to the prompt.
-# If your stuck and MySQL is returning <code>-></code> at any time, you have forgotten the ; at the end of the command. Type the command with a ;. This will display an error message. Type the command again with the ; and the command will work
+# If your stuck and MySQL is returning <code>-></code> at any time, you have forgotten the ; at the end of the command. Type the command with a ;. This will display an error message. Type the command again with the ; and the command will work.
+== Configuring MySQL ==
+There are a few things you may want to change with MySQL, depending on how you plan on using MySQL.
+=== InnoDB separate tables ===
+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.
+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:
+<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 (values edited out for security):<br></li>
+ # The MySQL server
+ [mysqld]
+ port            = 3306
+ socket          = /<dir>/mysql.sock
+ # Change following line if you want to store your database elsewhere<br>
+ # datadir = /<dir>
+ skip-external-locking
+ key_buffer_size = <num>M
+ max_allowed_packet = <num>M
+ table_open_cache = <num>
+ sort_buffer_size = <num>K
+ net_buffer_length = <num>K
+ read_buffer_size = <num>K
+ read_rnd_buffer_size = <num>K
+ myisam_sort_buffer_size = <num>M
+<li>At the end of the block, you will need to add the following:</li>
+ innodb_file_per_table=1
+<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>* 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>
+</ol>
 == External ==
 [http://www.mysql.com MySQL website]