STARIKOVS

Archive for the ‘mysql’ Category

Here is the sample about how to create a MySQL user and grant access to the database. In this example I use mysql command line tool.

First, let’s create a MySQL user:

  CREATE USER 'user_name'@'%' IDENTIFIED BY 'user_password';

After creating a database user let’s grant access to the database for the created user, so that the user can have all rights on the database:

  GRANT ALL PRIVILEGES ON mydatabase.* TO 'user_name'@'%';

More info about creating and granting access in MySQL here http://dev.mysql.com/doc/refman/5.5/en/adding-users.html

This note is about how to create a backup of MySQL database with help of command line tools and then how to restore it on the server.

So, the first step is making a dump of the DB. For that task we use mysqldump command. Here’s the code:

    mysqldump -h localhost -u root mydatabase > mydatabasebackup.sql

After dumping we will have SQL file that contains SQL command to recreate a database structure and SQL commands to restore all the data.

To restore a database out of backup we should use mysql command. It should be like in the following example:

    mysql -h localhost -u root -p -D mydatabase < mydatabasebackup.sql

It will ask you for the password.

By the way, before restoring your database you should create it in case that the database doesn’t exist.

1
2

    mysql -h localhost -u root -p
    > create database mydatabase;

I’ve just wanted to update the table’s field from the SELECT based on the other table including grouping and aggregate function. In my case, I need to update a TIMESTAMP field in tableA based on selected TIMESTAMP field from the tableB.

1
2
3
4
5

  UPDATE tableA SET timestampFieldA = 
    (SELECT MAX(timestampFieldB) 
     FROM tableB 
     WHERE tableA.id=tableB.tableAId 
     GROUP BY tableB.tableAId)

After MySQL’s been installed you can access it only from localhost (). It’s configured in my.cnf, in [mysqld] section. Here’s how this setting’s looked by default:

1
2

  # only localhost
  bind-address = 

If bind-address is omitted or set to 0.0.0.0 all other clients will be able to access this MySQL server.

1
2

  # all the clients
  bind-address = 0.0.0.0

Specifying a certain IP address you say to MySQL server to listen connections only from this IP.

1
2

  # for example, only  IP
  bind-address = 

As for me, MySQL server isn’t started if I set a specific IP in bind-address option. Maybe that’s because it bans connections from localhost. Okay, in this case, it’s possible to use a firewall to allow access for one or more IP addresses and set bind-address to 0.0.0.0.

So, to connect to your database remotely from any IP address you should allow your MySQL server to listen all the connections by commenting bind-address or setting it to 0.0.0.0. To enable a remote access only for a particular client you should set bind-address to client’s IP or use a firewall. Using a firewall allows you to set access for one IP address or for multiple IP addresses.

Your changes to my.cnf will be applied only after restarting MySQL server. To reboot MySQL server type this command:

1
2

  # on Debian linux, in my case
  /etc/init.d/mysql restart

Or:

1
2

  # Ubuntu
  sudo service mysql restart

Also, if you want to reboot the mysql server you may stop and then start the service:

1
2
3
4
5

  # Stop
  sudo stop mysql
 
  # Start
  sudo start mysql

Today I downloaded MySQL Workbench 5.2.31 CE. I usually work with different languages and I found that SQL Editor didn’t show russian letters correctly. I went to the Preferences but there I found that I can’t change the font. In the Preferences you can only see what the font is used but you cannot change it. Fortunately, there’re a lot of config files in XML format you can change. Before change any setting file you should close the Workbench. I use Windows 7 and to change the font I oppened this file C:\Users\User\AppData\Roaming\MySQL\Workbench\wb_options.xml and found this key workbench.general.Editor:Font and changed it to the value I needed. Here is what I have in my wb_options.xml:

 type="string" key="workbench.general.Editor:Font">
  Verdana
>

Just remember, before changing this option close the MySQL Workbench and after the changing start it, doing this you will avoid your option to be overwritten.

Hi! This article is about how to execute some addtitional SQL after the primary SQL in the CodeCharge Studio’s Record has been executed.

Very often I have to perform some additional manipulations with the database after the Record submission. In this article I want to share the approach I use to reach the desired result. If you use a different approach to reach the same result I’m waiting your comments!

Read the rest of this entry »