In case you remove a lot of data from the tables OR change the database structure, a de-fragmentation/optimizing of the database is necessary to avoid performance loss, especially while running queries. The above changes results in a performance loss, so make sure you run the “optimizer” on the database. SSH to your server and execute: [...]
This tutorial explains how you can install MySQL Proxy on a CentOS 5 (x86_64) system. MySQL Proxy is a simple program that sits between your client and MySQL server(s) that can monitor, analyze or transform their communication. Its flexibility allows for unlimited uses; common ones include: load balancing; failover; query analysis; query filtering and modification; [...]
MySQL is one of the most important programs on a server, unfortunatly it is also pretty resource intensive. One a server it is not uncommon for a single user or even a query to take up the bulk of the servers cpu cycles. Mytop is a very useful program to see what queries a server [...]
In SQL Server Management Studio navigate to your database. Right click it and select “Tasks” -> “Generate Scripts” “Next” Select your database from the list “Next” Select “Stored Procedures” “Next” “Select All” “Next” “Script to new Query Window” “Next” “Finish” Give it a while. Then when complete, at the very top of the script put [...]
MySQL replication will stop if an error occurs when running a query on the slave. The reason is so you can resolve the problem, thus keeping the data consistent with the master. You can skip those errors if you know those queries and why they are failing. Skip one query mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; [...]
How about backing up all the databases in the server? That’s an easy one, just use the –all-databases parameter to backup all the databases in the server in one step. mysqldump –all-databases> alldatabases.sql How to Backing up only the Database Structure in MySQL You can backup only the database structure by telling mysqldump not to [...]
This comes up in discussions almost every new project I work on, because it’s a very important thing to consider when designing a database. When deciding when and how to create an index in your MySQL database, it’s important to consider how the data is being used. Let’s say you have a database of employees. [...]
1.Create database with same name as MDF file you have. USE [master] GO ALTER DATABASE [MyDatabase] SET EMERGENCY GO ALTER DATABASE [MyDatabase] SET SINGLE_USER GO 2.Stop SQL Server and swap MDF files. Make sure you also keep new database you just created. 3.Start SQL Server. Database will be now in suspect state because log file [...]
Unofficial, No mess, No thrills, No fluff, No Explanation, Absolutely No Guarantee, Ruff and Gruff howto fix MySQL Databases – Make a temp directory to work in cd /root mkdir mysql_tmp cd mysql_tmp – Locate the database, ussualy a directory in /var/mysql or /var/lib/mysql ls -la /var/mysql/_database_name_ – Stop the mysql service, might be called [...]
Changing the collation for all tables in a MySQL database can be time consuming depending on how many tables you have. That’s why we recommend using the following PHP script for changing the collation for all tables at a time: <?php $db = mysql_connect(‘localhost’,’myuser_mydbuser‘,’mypassword‘); if(!$db) echo “Cannot connect to the database – incorrect details”; mysql_select_db(‘myuser_mydbname’); [...]
