Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 13

Displaying posts with tag: Beginner (reset)

Beginning on the MySQL Command Line
Employee_Team +0 Vote Up -0Vote Down

If you are starting out on the MySQL command line client, you might be interested in the following information.

When you use the MySQL command line client, it is often useful to log the commands you enter and the response you receive back from the MySQL server. The MySQL 'tee' command allows you to do this very simply and conveniently. Just enter 'tee' followed by the path to the file you want to log the session to (normally referred to as the 'tee' file). From that point on, all your session activity is logged to that file. To cancel tee file logging, just execute 'notee'.

To learn more about getting started on the MySQL Database, take the MySQL for Beginners training course. You can

  [Read more...]
Removing all databases from a MySQL instance
+1 Vote Up -1Vote Down

Many out there will have different ideas about this, some using procs, some using a function, others using a shell script. Well I didn’t want to spend much time on it so decided a group_concat(concat would be enough.
There is no genius, rather laziness :) but what if you have a hundred databases and you want to drop them all?

mysql Thu Mar  3 13:50:06 2011 > pager sed 's/,/ /g'
PAGER set to 'sed 's/,/ /g''
mysql Thu Mar  3 13:50:32 2011 > select group_concat(concat('drop database ',SCHEMA_NAME,';')) from information_schema.schemata where SCHEMA_NAME !='mysql' and SCHEMA_NAME !='information_schema';
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| group_concat(concat('drop database '


  [Read more...]
select *,colname | select colname,* that is the question
+3 Vote Up -0Vote Down

Strangely enough, select *,colname from tbname; works just fine, whereas select colname,* from tbname; fails! So if you would like to see all columns, but have one or more of them displayed to the left of the set, you have only one way left to do it ‘comfortably’:

select colname,tbname.* from tbname; well two really but this is really the same
select colname,tb.* from tbname tb;

Because Sharing is Caring

MySQL anonymous accounts – User=”, Host=’%’ – CODE RED
+0 Vote Up -0Vote Down

I want to highlight the importance of reviewing mysql’s initial set of accounts.
Say you have a mysql on abc.def.ghi.jkl running on port 3306 anonymous account with privileges without a password, then:
1. mysql (if issued on localhost)
2. mysql -h abc.def.ghi.jkl
3. mysql -u ” -h abc.def.ghi.jkl
4. mysql -u ” -h abc.def.ghi.jkl -P 3306
5. mysql -u user_which_does_not_exist -h abc.def.ghi.jkl

will all manage to get into mysql given the way mysql authenticates users is against your username and client host from where you are connecting.

This verification is done versus the following columns in the mysql.user table, i.e., User,Host and Password columns.
An entry in the mysql.user table with the following values User=”, Host=’%’ will accept ANY user connecting from ANYWHERE







  [Read more...]
MySQL related bookmark collection
+2 Vote Up -0Vote Down

I am publishing my MySQL related bookmark collection http://www.mysqlpreacher.com/bookmarks/.

Feel free to send me links you think might be good to add in order to help others.

Remember, SHARING IS CARING!!! …. we get so much for free, why shouldn’t we give some back?

Cheers,
Darren

Securich – 0.1.4
+0 Vote Up -0Vote Down

Just a small note to advise that Securich reached 0.1.4.
Some new tools include:
* Added Password complexity
* Enhanced `set_password` – Old password is now necessary to replace it by a new one
* Enhanced Revoke privileges to accept regexp
* Added Block user@hostname on a database level
* Added Creation of reserved usernames
* Added Help stored procedure displays help for each stored proc
* Enhanced `create_update_role` to include the removal of privilages from roles
* Enhanced `grant_priveleges` on `alltables` for a database without tables would terminate with an error instead of gracefully (now fixed)
* Added Restore user@hostname on a database level
* Removed ’show warnings’ from sql installation

The database design using workbench












  [Read more...]
MySQL processlist – (show/kill processes)
+1 Vote Up -0Vote Down

It”s not the most common task in the world, but you might want to view processes from a particular user and once in a while you might even need to kill processes from a single user, be it during an attack or because you simply got a bug in an application bombarding your db server with connections!

Here is a small stored procedure which does exactly that!

call process_list("show","username","hostname");

– shows all processes owned by username@hostname

call process_list("kill","username","hostname");

– kills all processes owned by username@hostname

The code for this stored procedure can be found below. If you have any comments / suggestions feel free to comment below.

  [Read more...]
Save time and energy: How to … (… continued)
+0 Vote Up -1Vote Down
Earlier this year I had published a small blog about being efficient when using mysql prompt. This is a small continuation of it highlighting a couple of other cool features which I really find very useful when working command line (i.e. always!). The first I’m gonna list here is setting the prompt itself by typing – \R [...]
MCDBA Certification – KPIs
+0 Vote Up -0Vote Down
A few interesting KPIs about certified people and their current location (the fact that many people travel from one country to the other for work is very popular these days especially in the IT sector): Before starting to read, I am hereby assuming the MySQL list of MCDBAs is on a residence basis not citizenship. There are [...]
Save time and energy: How to …
+0 Vote Up -0Vote Down

Save time and energy: How to:

Listening to “Highway to Hell” in an Oxford University computer lab on a sunday afternoon writing a MySQL blog can be legitimally defined by some as sick but thats what a geek calls a relaxing yet productive sunday afternoon.

For the sake of all those CLI ambassadors like myself, I wanted to share a couple of tips and tricks I use quite often when doing my mysql db administration work / scripting. I strongly suggest any mysql dba / dev use CLI simply because thats the one thing you should always have on any machine on which mysql is installed, be it Unix, Linux, Mac, Microsoft or whatever platform you are using.

Cancelling a query you are typing without exiting to the terminal:
`\c` – How many times you ended up hitting `CTRL+C` (default process kill in most OSes) in order to cancel a command, finding

  [Read more...]
Slap’em
+0 Vote Up -0Vote Down

Giving a bunch of mysql instances something you do everyday and you might think ….. how should I do it? Write a bunch of selects and inserts manually? nahh that takes s**tload of time, should I run binlogs collected from a live system on my test server? nahh thats not practical nor is it real since it doesn’t contain selects, should I gather the general query log and try that out? nahhh  …..

MySQL has been kind enough to supply us with their mysql_slap which does the job for us and given I needed to do a proof of concept on monitoring a group of 4 circular replicated servers I wrote a small script which does the job of slapping them with a varying level of concurrancy, iterations, number of queries and connections for as long as you like.

Here it is and I hope some of you might find it useful for slapping their own test servers :).

  [Read more...]
MySQL Installing: Binary tarball (.tar.gz) *nix based platforms
+0 Vote Up -0Vote Down

Installing MySQL is quite an easy thing to do, especially when done using pkgs, dmgs or exes. It gets just a tad more time consuming and brain intensive when installing a .tar.gz binary package. It is when you’re compiling MySQL source directly that you’ll need some planning and playing, but the latter is only done in particular cases such as when you’ll need a particular engine not shipped with a pre-compiled package etc.

Today we’re going through the steps required for a typical MySQL installation from a .tar.gz package on a *nix based platform, including the download, installation, configuration and securing.

Steps involved:
1. Download MySQL binary tarball from mysql.com
2. Create a folder structure where the installation will be held.
3. Install the package
4. Secure the



  [Read more...]
Table Sizes
+0 Vote Up -0Vote Down

During the course of my daily work I occasionally search for mysql queries which are cool and helpful. I once found the following query on http://forge.mysql.com/:

SELECT table_name article_attachment,
engine,
ROUND(data_length/1024/1024,2) total_size_mb,
ROUND(index_length/1024/1024,2) total_index_size_mb,
table_rows
FROM information_schema.tables
WHERE table_schema = ‘dbname’
ORDER BY 3 desc;

A generally  lite version is:

select table_schema, table_name, (data_length)/pow(1024,2) AS ‘Data Size in Meg’, (index_length)/pow(1024,2) AS ‘Index Size in Meg’  from tables order by 3 desc;

You can add or remove columns etc and but this query shows the table size (data wise) index size, approx number of rows, size in MB etc. If you would like to know what else is available to add to this query, just do a “desc tables” while using the informations chema (database).

Showing entries 1 to 13

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.