Menu Close

Date format in MySQL

Hi guys, today let’s talk about how to format date in MySQL For this tutorial I’m using mysql server 5.5 Basically you need to know how to use a function called DATE_FORMAT Let’s create a table and insert some rows: CREATE TABLE IF NOT EXISTS `dates` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ; INSERT INTO `dates` (`id`, `date`) VALUES (1, '2012-10-17 13:39:55'), (2, '2012-10-09 09:23:11'), (3, '2012-08-24 16:47:07'); OK? then let’s select this values and show it formated SELECT id, DATE_FORMAT( `date` , '%d/%c/%Y %H:%i:%s' ) AS `date` FROM `dates` It’s easy, just remember which the format is always %ONE_LETTER, where this letter is the field which you want to show (see . . .

Liked? Help and Share!

How to change user password on MySQL

Today let’s talk about how to change MySQL user password We can use 2 ways, 1 – mysqladmin, 2 – linguagem SQL This is a step by step video of this post: 1. mysqladmin: The syntax is easy: mysqladmin -u USER -p password NEWPASSWORD Let’s then change the password of ‘marcelo’ user to ‘123’ mysqladmin -u marcelo -p password '123' For this command, we have 3 problems: . You can just change your own user . You need SUPER PRIVILEGES to run this command . If you share you linux user account with other users, this command will appear on historic, to avoid it we can edit ~/.bash_history and delete this lines 2. SQL (the best on my opinion): To change the password, we’ll just run an update on user’s . . .

Liked? Help and Share!

Using the Mysql FullText Index Search

Today let’s talk about a resource very useful on MySQL, the FullText Index and Search This resource is very powerful, today on versions 5.5 is just available to MyISAM engine, but, like we can see on MySQL FullText documentation, it will be available also to InnoDB on MySQL 5.6 Usually when we want to search for a word or expression, we use LIKE ‘%word%’, in case we are looking for more than one word we use LIKE ‘%word1%word2%’, what many people don’t know is for this kind of search is expensive and not optimized to our MySQL, in this cases we solve our problems with FullText Index the syntax is easy, MATHC() … AGAINST (), where MATCH we specified the name(s) of column(s) which we are looking for, yes, we . . .

Liked? Help and Share!

How to rename a database in MySQL?

Today we will talk how do you can easily rename a database in MySQL. At the MySQL version 5.1.7 was added a command to do this job named RENAME DATABASE RENAME {DATABASE | SCHEMA} db_name TO new_db_name; But it was removed on the MySQL version 5.1.23 because maybe it would result in data lost’s. Then, let’s see some ways in which we can do this: 1. Using the RENAME TABLE command As long as two databases are on the same file system, you can use RENAME TABLE to move a table from one database to another: RENAME TABLE current_db.tbl_name TO other_db.tbl_name; RENAME TABLE Syntax na Documentacao do MySQL mysql> show tables; +———————+ | Tables_in_oldSchema | +———————+ | tb1 | | tb10 | | tb2 | | tb3 | | tb4 . . .

Liked? Help and Share!

MySQL replication – Master – Slave

To explain the replication, we will use the article Installing MySQL 5.5 Part 2 – Multiple instances with mysqld_multi because we will need 2 MySQL’s running on our server Let’s activate the binary logs on our server, creating a file named my_rep.cnf with the follow content: [mysqld_multi] mysqld = /mysql/mysql/bin/mysqld_safe mysqladmin = /mysql/mysql/bin/mysqladmin [mysqld1] port = 3306 datadir = /mysql/3306/data socket = /mysql/3306/mysql.sock log-error = /mysql/3306/logs/mysqld.log pid-file = /mysql/3306/mysqld.pid server_id = 1 #LOGS log-bin = /mysql/3306/logs/server1_bin.log log-bin-index = /mysql/3306/logs/server1_log-bin.index expire_logs_days = 7 binlog-format = MIXED [mysqld2] port = 3307 datadir = /mysql/3307/data socket = /mysql/3307/mysql.sock log-error = /mysql/3307/logs/mysqld.log pid-file = /mysql/3307/mysqld.pid server_id = 2 #LOGS relay-log = /mysql/3307/logs/server2.relay_log relay-log-index = /mysql/3307/logs/server2.relay_log_index expire_logs_days = 7 Done this, let’s start the MySQL service like our article: cd /mysql/mysql mysqld_multi –defaults-file=/mysql/my_rep.cnf start Now, . . .

Liked? Help and Share!

MySQL query optimize with query cache

  EDIT: Please note that query cache is been a know scalability issue for MySQL in high concurrency environments.  Query cache has been removed from MySQL on version 8.0 . Use with care!   Today let’s talk about a very cool tool of MySQL, the query cache. The query cache saved the SELECT results, that’s already executed their raw data have not changed thus making the response time of query much more optimized, because it will fetch from memory rather than disk. It can work in 3 different ways, in our my.ini within the group [mysqld]: query_cache_type = 0 Off query_cache_type = 1 On to all the query’s query_cache_type = 2 On demand But how best to use the query cache? Considering that every time you change data in a table, it . . .

Liked? Help and Share!

MySQL compress backup – mysqldump – gzip – bzip2

Today let’s talk about a very important issue for database administrators, BACKUP. There are several strategies for backup, snapshot, dump, dump binlog, it all depends on your need and the size of your database. I’ll explain a little about the tool provides by MySQL, which is the mysqldump , I indicate this tool for databases that have 15gb/20gb even more than that, the probability that you have problems to restore a backup and long delay, make it unfeasible. How mysqldump works? He does nothing but create sql commands to our structure and data and put them in the file specified. First, I will explain some of the most important options mysqldump then, for those who have MySQL installed on a Linux server, I will teach how to compress the dump . . .

Liked? Help and Share!

Installing MySQL 5.5 Part 2 – Multiple instances with mysqld_multi

Continuing our  previous post, today we’ll go up several instances of MySQL on the same server, very common practice today, instead of creating a single instance in100% of the resources of the server splits the load of QPS (queries per seconds) between multiple instances. The groups will look for mysqld_multi [mysqldN] within our my.cnf configuration file, where N is an integer that will be used later to refer to what server we’ll see the STOP or START REPORT We have the following structure on our server: | _3306 / | _data / | _logs / Let’s create the structure of the 3307 instance and set her rights: mkdir 3307 mkdir 3307/data mkdir 3307/logs chown-R mysql: mysql / mysql/3307 We will now create the default structure of the script contained in the database foldermysql cd / mysql / mysql / scripts / . / mysql_install_db – user = mysql – basedir = / mysql / mysql – datadir = /mysql/3307/data / Recalling that we must be careful to get the OK’s 2 below: Installing MySQL system tables … OK Filling help tables … OK That done, we go to our configuration file, the my.cnf, today it looks like this: [mysqld] datadir = / mysql/3306/data socket = / mysql/3306/mysql.sock user = mysql [mysqld_safe] log-error = / mysql/3306/logs/mysqld.log pid-file = / mysql/3306/mysqld.pid Now let’s add a new group, called [mysqld_multi] it will put three items: mysqld – path to our binary mysql, I suggest here, put the path to . . .

Liked? Help and Share!

Installing MySQL 5.5 Part 1 – A single instance

Today I will teach you how to install a MySQL server with ease. With only a single instance in the second part of this article, I will teach installing multiple instances of MySQL and control them through the mysqld_multi. For this tutorial I’m using: VMWare CentOS 5.6 – 64bits 512MB RAM 40GB DISCO Installation without a GUI, only command line That said, let’s get to what really matters: Let’s create a directory structure that we work with our installation of MySQL for this tutorial I will install the root partition: cd / mkdir mysql cd mysql We go to MySQL downlads site and download the latest version Community Server what is now 5.5.11. Use Build Linux Generhttp://twitter.com/#!/altmannmarceloic and search for the package tar.gz version of its operating system: wget http://mysql.cce.usp.br/Downloads/MySQL-5.5/mysql-5.5.11-linux2.6-x86_64.tar.gz . . .

Liked? Help and Share!

lost MySQL root password

If you have lost the MySQL root password, or just don’t remember it, don’t worry, follow the step by step for you to retrieve it Stop the MySQL service if it is running “/etc/init.d/mysql stop” Up the Server “skiping” the layer responsible for checking the user privileges mysqld_safe-skip-grant-tables , remembering , when you start the server with this option, it will accept any user to connect to database MySQL database with full access to all the databases, then to inhibit other people from connecting to the server during the maintenance period, I suggest you use the following command to start the server  mysqld_safe – skip-grant-tables – skip-networking. In this case the server not accept connections over TCP/IP Connect to server with the command mysql -u anything Enter update mysql.user set . . .

Liked? Help and Share!