Menu Close

Category: MySQL

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!