Editing MySQL

Jump to navigation Jump to search

Warning: You are not logged in. Your IP address will be publicly visible if you make any edits. If you log in or create an account, your edits will be attributed to your username, along with other benefits.

The edit can be undone. Please check the comparison below to verify that this is what you want to do, and then save the changes below to finish undoing the edit.

Latest revision Your text
Line 22: Line 22:
  
 
=== Connecting to mysql server from the [[shell]]. ===
 
=== Connecting to mysql server from the [[shell]]. ===
 
+
<pre>
shell> mysql -u root -p
+
shell> mysql -u root -p
Enter password:
+
Enter password:
Welcome to the MySQL monitor.
+
Welcome to the MySQL monitor.
mysql>
+
mysql>
 
+
</pre>
  
 
=== Switching into a database. ===
 
=== Switching into a database. ===
 
+
<pre>
mysql> use wikidb;
+
mysql> use wikidb;
Database changed
+
Database changed
mysql>
+
mysql>
 
+
</pre>
  
 
=== Showing tables ===
 
=== Showing tables ===
Line 87: Line 87:
 
You can use [[wildcards]] like in:
 
You can use [[wildcards]] like in:
 
   
 
   
mysql> select * from interwiki;
+
<pre>mysql> select * from interwiki;</pre>
  
 
=== Conditions (WHERE-clause) ===
 
=== Conditions (WHERE-clause) ===
Line 107: Line 107:
 
approximate match:
 
approximate match:
  
 +
<pre>
 
at beginning
 
at beginning
  
mysql> select cur_title from cur where cur_text LIKE "Fnord%";
+
mysql> select cur_title from cur where cur_text LIKE "Fnord%";
  
 
in the middle
 
in the middle
  
mysql> select cur_title from cur where cur_text LIKE "%Foobar%";
+
mysql> select cur_title from cur where cur_text LIKE "%Foobar%";
 
+
</pre>
  
 
=== Sorting ===
 
=== Sorting ===
Line 120: Line 121:
 
==== ascending: ====
 
==== ascending: ====
  
mysql> select rc_id,rc_title from recentchanges ORDER BY rc_id;
+
<pre>
 +
mysql> select rc_id,rc_title from recentchanges ORDER BY rc_id;
 +
</pre>
  
 
==== descending: ====
 
==== descending: ====
  
mysql> select rc_id,rc_title from recentchanges ORDER BY rc_id DESC;
+
<pre>
 +
mysql> select rc_id,rc_title from recentchanges ORDER BY rc_id DESC;
 +
</pre>
  
 
=== Limiting ===
 
=== Limiting ===
 +
 
<pre>
 
<pre>
  
Line 149: Line 155:
 
+-------+
 
+-------+
 
4 rows in set (0.00 sec)
 
4 rows in set (0.00 sec)
 +
 
</pre>
 
</pre>
  
Line 232: Line 239:
 
Examples:
 
Examples:
  
 +
<pre>
 +
mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost' IDENTIFIED BY 'goodsecret' REQUIRE SSL;
 +
</pre>
  
mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost' IDENTIFIED BY 'goodsecret' REQUIRE SSL;
+
<pre>
 
+
mysql> GRANT SELECT ON foo.bar TO 'fnord'@'somehost' IDENTIFIED BY 'somepass';
mysql> GRANT SELECT ON foo.bar TO 'fnord'@'somehost' IDENTIFIED BY 'somepass';
+
</pre>
 
 
  
 
These are random examples, for further syntax check:
 
These are random examples, for further syntax check:
Line 254: Line 263:
 
==== Setting the initial root password ====
 
==== Setting the initial root password ====
  
 
+
<pre>
shell> mysql -u root mysql
+
shell> mysql -u root mysql
mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');
+
mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');
 +
</pre>
  
 
==== Reset forgotten root password ====
 
==== Reset forgotten root password ====
Line 292: Line 302:
 
=== Sending output to textfile ===
 
=== Sending output to textfile ===
  
mysql> select foo from bar INTO OUTFILE '/tmp/dump.txt';
+
<pre>
 +
mysql> select foo from bar INTO OUTFILE '/tmp/dump.txt';
  
bash> mv /tmp/dump.txt .
+
bash> mv /tmp/dump.txt .
  
 
(if trouble writing because mysql user has different permissions)
 
(if trouble writing because mysql user has different permissions)
  
 +
</pre>
  
 
=== Taking input from textfile ===
 
=== Taking input from textfile ===
  
 
+
<pre>
mysql> mysql -u root -p < dumpfile.sql
+
mysql> mysql -u root -p < dumpfile.sql
Enter password:
+
Enter password:
 +
</pre>
  
  

Please note that all contributions to s23 are considered to be released under the Creative Commons Attribution Share Alike (see s23:Copyrights for details) unless another license is specified on a specific page. If you don't want your writing to be edited mercilessly and redistributed at will, then don't submit it here.
When uploading your own work you can choose from different cc license types, a GNU license like GFDL or you can declare it entirely Public domain. Use the according license template on a page. If no specific license is specified, Creative Commons Attribution Share Alike will apply. You are also promising us that you wrote this yourself, or copied it from a public domain or similar free resource. DO NOT SUBMIT COPYRIGHTED WORK WITHOUT PERMISSION!

Cancel Editing help (opens in new window)