MySQL : Restore Database From the Command Prompt

mysql - u user_name -p your_password database_name < file_name.sql

MySQL : Backup Database From the Command Prompt

mysqldump -u user_name -p your_password database_name > File_name.sql

MySQL Function : mysql_affected_rows

Get number of affected rows in previous MySQL operation

Description
int mysql_affected_rows ([ resource $link_identifier ] )

Get the number of affected rows by the last INSERT, UPDATE, REPLACE or DELETE query associated with link_identifier .



Example #

= mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db('mydb');

/* this should return the correct numbers of deleted records */
mysql_query('DELETE FROM mytable WHERE id <>);
printf("Records deleted: %d\n", mysql_affected_rows());

/* with a where clause that is never true, it should return 0 */
mysql_query('DELETE FROM mytable WHERE 0');
printf("Records deleted: %d\n", mysql_affected_rows());
?>



The above example will output something similar to:

Records deleted: 10
Records deleted: 0

MySQL : Have the new Query Analyzer graphs helped you solve development problems internally?

We use it about every day for our own products, specifically for finding performance issues in development. For example, one of the things we found in this release was that we had more updates than we expected compared to the previous version. We learned it because we saw a peak in “updates” in the row activity graph in the Enterprise Monitor. We highlighted the peak in the updates, and we figured out exactly which part of our code related to these updates through the Query Analyzer, and finally we fixed the problem. This process would have taken much longer if we did it manually because it was actually a certain set of updates that were increasing at a certain point of time, while other normal updates were happening at the same time. If we just looked at the logs, it wouldn't have been as obvious which one was causing that increase.

In addition, our support team also runs the Query Analyzer on internal servers, and they actually found some problem queries. In some occasions they solve performance issues that they didn't know the root cause; in other situations they see the performance drop and have actually been able to tune some queries that they didn't even know were problematic.

MySQL : How do the Query Analyzer graphs benefit customers?

It saves a lot of time. The first release of the Query Analyzer saved a lot of time by removing the need to send query activity to logs, gather all that information, and post-process the data in order to figure out which queries were running, how often and for how long. The logical next step was correlating this information with the metrics the Enterprise Monitor collects and displays the data with graphs. If you see something happening performance-wise in the Enterprise Monitor, using the correlation graphs you can very quickly see what queries were happening at that time, before that time or even after that time from a query perspective in the Query Analyzer. That was possible to do in our earlier release, except that it was a very manual process.

MySQL : What type of metrics can be correlated with the Query Analyzer?

Any graph you see, anything we collect – that could be anything from operating system metrics, such as CPU usage, memory usage, I/O counters, to MySQL-specific counters. You can look at the query profiles over time, i.e. how many selects and updates and other classes of queries were running at any point of time, connections created, threads created, InnoDB row accesses, buffer pool, locks taken over time -- all of these are implemented in this correlation graph feature.

SQL Server 2008 Top New Features :Extended Events

The extended events infrastructure provides an in-depth troubleshooting tool that enables administrators to address difficult-to-solve problems more efficiently. Administrators can investigate excessive CPU usage, deadlocks, and application time outs as well as many other issues. Extended events data can be correlated with Windows events data to obtain a more complete picture that will aid in problem resolution.

• Provides detailed information about low level events in SQL Server

• Reduces the time required to troubleshoot complex problems

• Provides access to event data that has previously been difficult to obtain

SQL Server 2008 Top New Features :Backup Compression

Backup compression enables the backup of a database to be compressed without having to compress the database itself. All backup types, including log backups, are supported and data is automatically uncompressed upon restore.

• Save storage space

• Compressed backups can be stored on tape or on disk

• Simple configuration using SQL Server Management Studio

• Default state of all backups on a server to be compressed can be configured

SQL Server 2008 Top New Features :Partition Aligned Indexed Views

Indexed Views let SQL Server persist the results of a view, instead of having to dynamically combine the results from the individual queries in the view definition. Indexed Views can now be created to follow the partitioning scheme of the table that they reference. Indexed views that are aligned in this manner do not need to be dropped before a partition is switched out of the partitioned table, as was the case with SQL Server 2005 indexed views.

• Indexed views improve performance with very large partitioned tables, such as fact tables in data warehouses.

SQL Server 2008 Top New Features :Upgrade Advisor

The Upgrade Advisor generates a report that highlights any issues that might hinder an upgrade. This provides administrators detailed information that can be used to prepare for upgrades.

• Helps streamline the upgrade process by identifying upgrade issues in advance

• Can be used to analyze both local and remote systems

• Upgrade Advisor reports provide links to technical information that assist in addressing upgrade problems

SQL Server 2008 Top New Features :Server Group Management

Server Group management enables T-SQL queries to be issued against multiple servers from a single Central Management Server, which simplifies administration. Stream results of multi-server queries into a single result set or into multiple result sets enables the option of evaluating policies against a server group.

• Management is centralized, so servers do not need to be configured individually

• Provides a simpler administration model for policy evaluation

SQL Server 2008 Top New Features : Streamlined Installation

The SQL Server 2008 installation process has been streamlined to be easier and more efficient. Individual SQL Server components, such as Database Services, Analysis Services, and Integration Services, can be optionally selected for installation. Failover cluster support configuration has also been added to the installation.

• Easier to install SQL Server

• Setup now advises of configuration problems such as installation pre-requisites, which helps streamline the installation process

SQL Server 2008 Top New Features: Hot-Add CPUs and Hot-Add Memory

Hot-add CPUs, a feature available with the 64-bit edition SQL Server Enterprise, allows CPUs to be dynamically added to servers as needed, without the need to shut down the server or limit client connections. Hot-add memory enables memory to be added in the same way.

• Dynamically add memory and processors to servers without incurring downtime

SQL Server 2008 Top New Features : Data Auditing

Data Auditing provides a simple way to track and log events relating to your databases and servers. You can audit logons, password changes, data access and modification, and many other events. Tracking these events helps maintain security and can also provide valuable troubleshooting information. The results of audits can be saved to file or to the Windows Security or Application logs for later analysis or archiving.

• Enables compliance with security regulations

• Simple configuration using SQL Server Management Studio

• Minimal impact on performance because audit data is stored outside of SQL Server database files

External Key Management / Extensible Key Management

External Key Management enables certificates and encryption keys to be stored using third-party hardware security modules that are designed specifically for this purpose. Storing the keys separately from the data enables a more extensible and robust security architecture.

• Helps organizations comply with data privacy regulations

• Reduces administration requirements when there are multiple keys and certificates to manage

• Standard interface supports third party hardware security modules

SQL Server 2008 Top New Features : Transparent Data Encryption

Transparent Data Encryption enables data to be stored securely by encrypting the database files. If the disks that contain database files become compromised, data in those files is protected because that data can only be de-encrypted by an authorized agent. SQL Server performs the encryption and de-encryption directly, so the process is entirely transparent to connecting applications. Applications can continue to read and write data to and from the database as they normally would. Backup copies of encrypted database files are also automatically encrypted.

• Implements strong encryption keys and certificates to secure data

• Applications do not need to be modified to support Transparent Data Encryption

• Enables compliance with data privacy regulations

• Does not increase the size of the database

SQL Server 2008 Top New Features :Resource Governor

The Resource Governor enables administrators to control and allocate CPU and memory resources to high priority applications. This enables predictable performance to be maintained and helps avoid performance from being negatively affected by resource-intense applications or processes

• Prioritize applications, users, and computers competing for the same resources

• Prevent runaway queries that hold resources for extended periods of time

• Limitations are not enforced when there is no competition for resources

SQL Server 2008 Top New Features :Data Compression

Data compression reduces the amount of storage space needed to store tables and indexes, which enables more efficient storage of data. Data Compression does not require changes be made to applications in order to be enabled.

• Save disk storage

• Enable compression option for individual tables or indexes

• Configuration is easy using the Data Compression wizard

• Applications do not need to be reconfigured as SQL Server handles compression and decompression of data

• Compression can improve disk I/O and memory utilization

SQL Server 2008 Top New Features : Performance Data Collection (Data Collector)

The Data Collector provides a convenient way to collect, store, and view performance data automatically. It collects disk usage, server activity, and query statistics data, which it loads in a management data warehouse and performance data can be reviewed in SQL Server Management Studio or by using third-party tools.

• Setup wizard makes configuration simple

• Data collection is automated by using set of SQL Server Agent jobs and SQL Server Integration Services packages

• Management is centralized so data collection can be easily configured and results can be viewed in one place

• Performance impact is minimal because the data collected can be cached and uploaded to the data warehouse later

SQL Server 2008 Top New Features : Policy-Based Management

Policy-Based Management enables the efficient management of multiple SQL Server instances from a single location. Easily create policies that control security, database options, object naming conventions, and other settings at a highly granular level. Policies can evaluate servers for compliance with a set of predefined conditions and prevent undesirable changes being made to servers.

• Management is centralized, thereby reducing the need to configure each server separately

• Administration is simplified, reducing the effort required to maintain standardization and compliance, even in complex environments

• Configuration is straightforward and can be done entirely within SQL Server Management Studio

• Out-of-the-box predefined policies make it easy to get started

• Backwards compatibility supports managing instances of SQL Server 2008, SQL Server 2005, and SQL Server 2000